I have a requirement for weekly stats and am able to group-by using WEEK() function which gets me a week-of-year number but my I need the first date of the week instead.
Timestream table:
| user | duration | time |
|---|---|---|
| wayne | 122 | 2024-01-01 10:30:45 |
| steve | 42 | 2024-01-26 15:36:12 |
| jack | 21 | 2024-01-31 07:21:31 |
| shannon | 07 | 2024-02-01 10:52:16 |
| harvey | 70 | 2024-02-06 22:42:01 |
| harvey | 50 | 2024-02-07 04:18:34 |
Required output:
| week_start_date | duration_sum |
|---|---|
| 2024-01-01 | 122 |
| 2024-01-22 | 42 |
| 2024-01-29 | 28 |
| 2024-02-05 | 120 |
What I have done so far:
SELECT WEEK(time) AS week_start_date, SUM(duration) as duration_sum FROM "db"."table" GROUP BY 1 ORDER BY 1
| week_start_date | duration_sum |
|---|---|
| 1 | 122 |
| 4 | 42 |
| 5 | 28 |
| 6 | 120 |
Need help in converting week-number to a date