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

Question

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"?

Answer

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:

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()))
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()))

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

Relevanter Inhalt