CODEWITHSUNDEEP
sqlamazon-redshift
user9302275
user9302275

Reputation:

Customizing the range of a week with date_trunc

I've been trying for hours now to write a date_trunc statement to be used in a group by where my week starts on a Friday and ends the following Thursday.

So something like 

SELECT
DATE_TRUNC(...) sales_week,
SUM(sales) sales
FROM table
GROUP BY 1
ORDER BY 1 DESC

Which would return the results for the last complete week (by those standards) as 09-13-2019.

Upvotes: 1

Views: 460

Answers (3)

AlexYes
AlexYes

Reputation: 4208

if by any chance you might have gaps in data (maybe more granular breakdowns vs just per week), you can generate a set of custom weeks and left join to that:

drop table if exists sales_weeks;
create table sales_weeks as
with 
 dates as (
    select generate_series('2019-01-01'::date,current_date,interval '1 day')::date as date
)
,week_ids as (
    select 
     date
    ,sum(case when extract('dow' from date)=5 then 1 else 0 end) over (order by date) as week_id
    from dates
)
select 
 week_id
,min(date) as week_start_date
,max(date) as week_end_date
from week_ids 
group by 1
order by 1
;

Upvotes: 0

rd_nielsen
rd_nielsen

Reputation: 2459

The expression

select current_date - cast(cast(7 - (5 - extract(dow from current_date)) as text) || ' days' as interval);

should always give you the previous Friday's date.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can subtract 4 days and then add 4 days:

SELECT DATE_TRUNC(<whatever> - INTERVAL '4 DAY') + INTERVAL '4 DAY' as sales_week,
       SUM(sales) as sales
FROM table
GROUP BY 1
ORDER BY 1 DESC

Upvotes: 3

Related Questions