sql - Can you define a custom "week" in PostgreSQL? -
i know in order extract week of of given year can use:
select extract(week timestamp '2014-02-16 20:38:40'); however, trying group weeks in bit of odd format. start of week begin on mondays @ 4am , conclude following monday @ 3:59:59am.
ideally, create query provides start , end date, groups total sales period weeks laid out above.
example:
select (some custom week date), sum(sales) salestable startdate between 'date 1' , 'date 2' note: not looking change extract() function, rather create query pull following sample table , output sample results.
if date 1 in query '2014-07-01' , date 2 '2014-08-18':
sample table:
itemid | timesold | price ------------------------------------ 1 | 2014-08-13 09:13:00 | 12.45 2 | 2014-08-15 12:33:00 | 20.00 3 | 2014-08-05 18:33:00 | 10.00 4 | 2014-07-31 04:00:00 | 30.00 ideal sample output:
weekbegin | pricetotal ---------------------------------- 2014-07-28 04:00:00 | 30.00 2014-08-04 04:00:00 | 10.00 2014-08-11 04:00:00 | 32.45
this postgres requested in title.
select date_trunc('week', time_sold - interval '4h') + interval '4h' week_begin ,sum(price) price_total tbl time_sold >= '2014-07-01 0:0'::timestamp , time_sold < '2014-08-19 0:0'::timestamp -- start of next day group 1 order 1; produces desired output exactly.
sql fiddle (extended row shows difference).
explain
date_trunc()superior tool here. not interested in week numbers, in actual timestamps.the trick subtract 4 hours selected timestamps before extracting week - thereby shifting time frame towards earlier border of iso week.
to produce display desire, add 4 hours truncated timestamps.
but apply
wherecondition on unmodified timestamps. also, never usebetweentimestamps, have fractional digits. usewhereconditions presented above. compare:operating data type
timestamp, i.e. (shifted) "weeks" according current time zone. additional considerations applytimestamptz. more:
Comments
Post a Comment