Can you define a custom "week" in PostgreSQL?

Question

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

Answer 1

This is for Postgres as requested in the title.

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;

Produces your desired output exactly.

db<>fiddle here (extended with a row that actually shows the 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 samme 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:

• Unexpected results from SQL query with BETWEEN timestamps

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:

• Ignoring time zones altogether in Rails and PostgreSQL