CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
Liondancer
Liondancer

Reputation: 16469

Group daily items by week from starting date

I have daily rows that I would like to group by weeks. Starting from a date, I want to group from 11/27/2017 + 7 days (milliseconds in a week) up to a certain date. How do I do this in Amazon Redshift?

Expected output:

             counts
2017-11-27     100
2017-12-04     200
2017-12-11     300
2017-12-18     400

Upvotes: 0

Views: 2847

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269284

You can use the date_trunc() function to convert a date into the start of a week (Monday). If you need to group by a different day (eg Sunday), you will need to offset the date, then do a date_trunc(), then add the day back again.

Here is an example from DATE_TRUNC Function - Amazon Redshift:

select date_trunc('week', saletime), sum(pricepaid) from sales where
saletime like '2008-09%' group by date_trunc('week', saletime) order by 1;
date_trunc  |    sum
------------+------------
2008-09-01  | 2474899.00
2008-09-08  | 2412354.00
2008-09-15  | 2364707.00
2008-09-22  | 2359351.00
2008-09-29  |  705249.00
(5 rows)

Upvotes: 2

Related Questions