sqlpostgresqltimestampdate-arithmetic

Can you define a custom "week" in PostgreSQL?

To extract the week of a given year we can use:

SELECT EXTRACT(WEEK FROM timestamp '2014-02-16 20:38:40');

However, I am trying to group weeks together in a bit of an odd format. My start of a week would begin on Mondays at 4am and would conclude the following Monday at 3:59:59am.

Ideally, I would like to create a query that provides a start and end date, then groups the total sales for that period by the weeks laid out above.

Example:

SELECT
   (some custom week date),
   SUM(sales)
FROM salesTable
WHERE
    startDate BETWEEN 'DATE 1' AND 'DATE 2'

I am not looking to change the EXTRACT() function, rather create a query that would pull from the following sample table and output the sample results.

If 'DATE 1' in query was '2014-07-01' AND 'DATE 2' was '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

Desired result:

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
Solution

  • Produces your desired output:

    SELECT date_trunc('week', time_sold - interval '4h')
                                    + interval '4h' AS week_begin
     , sum(price) AS price_total
FROM   tbl
WHERE  time_sold >= '2014-07-01 0:0'::timestamp
AND    time_sold <  '2014-08-19 0:0'::timestamp -- start of next day
GROUP  BY 1
ORDER  BY 1;

    fiddle - extended with a row that makes a difference
    Old sqlfiddle

    Explanation

    date_trunc() is the superior tool here. You are not interested in week numbers, but in actual timestamps.

    The "trick" is to subtract 4 hours from selected timestamps before extracting the week - thereby shifting the time frame towards the earlier bound of the ISO week. To produce the desired display, add the same 4 hours back to the truncated timestamps.

    But apply the WHERE condition on unmodified timestamps. Also, never use BETWEEN with timestamps, which have fractional digits. Use the WHERE conditions like presented above. See:

    Operating with data type timestamp, i.e. with (shifted) "weeks" according to the current time zone. You might want to work with timestamptz instead. See: