Query to get everything since midnight of most recent Sunday or everything after midnight today

0

Using Timestream with Grafana. I want a graph to display the count of records a machine has cycled (one record per cycle) on a daily basis. Displaying this data in last 24 hours is confusing because the bins keep changing based on current time. I need my binds to be calculated from midnight of that day.

My query looks like this now:

SELECT BIN(time, 1d), count() AS cycles
FROM "MfgRecords"."CycleCounts" 
where WorkCenter = 'MyMachine' and measure_name = 'Cycled' and measure_value::boolean = true
group by BIN(time, 1d)
order by BIN(time, 1d) desc

How would that be written?

Also, same question but for weeks. I want to display past weeks and the current week SO FAR since SUnday at Midnight. How?

Lastly, is there a time specification like "1d" or "15m" only for most recent dayname like "Sun"?

flycast
asked 2 years ago865 views
1 Answer
0

Hi

Based on the question I think date_trunc and date_add functions might be helpful here.

Timestream supports date_trunc and date_add functions that might help here. Sample output of these functions for today e.g. now() is below:

select now() as today, date_trunc('day', now()) as startofday, date_add('day', -1, date_trunc('week', now())) as startofweekMonday, date_add('week', 1, date_trunc('week', now())) as startofweekSunday

Output is

today, startofday, startofweekMonday, startofweekSunday
2022-05-02 19:08:45.649000000, 2022-05-02 00:00:00.000000000, 2022-05-01 00:00:00.000000000, 2022-05-09 00:00:00.000000000

--> date_trunc('week', ...) uses Monday as day of week. Using date_add function together with date_trunc will help to set the start of the week to Sunday.


For the questions:

  1. for fixing the start date to midnight of that day add a time predicate time >= date_trunc('day', now()) and time < date_trunc('day', date_add('day', 1, now()))

  2. for fixing the start of the week to sunday midnight of this week the time predicate is time >= date_add('day', -1, date_trunc('week', now())) and time < date_add('week', 1, date_trunc('week', now()))

AWS
SUPPORT ENGINEER
Kevin_Z
answered 2 years ago
  • I've been out of vacation and just saw this today. That looks exactly like the answer I was looking for. I will try this out as I get back in the saddle and let you know.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions