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

No you can't teach `extract()` to return something different (unless you change Postgres' source code). You need to write your own function (e.g. in PL/pgSQL) — , Aug 18 '14 at 18:00
Just subtract 28 hours from your timestamp, to offset Mondays at 4am to Sunday at midnight. — mbeckish, Aug 18 '14 at 18:04
@a_horse_with_no_name not looking to change the EXTRACT function, rather figure out a way to group by the defined weeks as noted above — JM4, Aug 18 '14 at 18:10
JM4 - My preference on these is a look up / reference table. Build a table that has week number, week_start_date, week_end_date. Join to this on a between condition to get the week number you want. — Twelfth, Aug 18 '14 at 18:28
@twelfth - great suggestion. The challenge comes in that we would like to run this across all US cities so the "week" timestamps become a bit trickier when crossing timezones — JM4, Aug 18 '14 at 18:41
Yes, that does become a bit trickier...pick a time zone that the report occurs in (eastern)? Convert everything to this eastern time zone when running the report...not sure how you want to preserve the time zone...you have conflicting tags, mysql or postgres? — Twelfth, Aug 18 '14 at 18:47
It is PostgreSQL but I figured it may be a question that could apply to either as strictly SQL theory — JM4, Aug 18 '14 at 18:51
@JM4 - What remaining questions do you have? Is it unclear how to calculate the weeknumber? Or are you unsure how to sum by week number? Or something else? — mbeckish, Aug 18 '14 at 19:39
@jm4 - postgres supports a date time stamp with time zone...I don't beleive MySQL does (in mysql's case, I think you need to store it as a seperate field in the table). Since you are on postgres, I'd use the time stamp with time zone option — Twelfth, Aug 18 '14 at 20:57

Erwin Brandstetter · Accepted Answer · 2022-10-25T09:44:56.433

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;

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 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:

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

Can you define a custom "week" in PostgreSQL?

1 Answers1

Explanation