- Newest
- Most votes
- Most comments
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()))
Relevant content
- asked 8 months ago
- Accepted Answerasked 2 months ago
- asked 2 years ago
- AWS OFFICIALUpdated 3 years ago
- AWS OFFICIALUpdated a month ago
- AWS OFFICIALUpdated a month 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.