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

Pail

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) – Semilunar

Just subtract 28 hours from your timestamp, to offset Mondays at 4am to Sunday at midnight. – Scar

@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 – Pail

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. – Stopped

@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 – Pail

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? – Stopped

It is PostgreSQL but I figured it may be a question that could apply to either as strictly SQL theory – Pail

@Pail - 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? – Scar

@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 – Stopped

Answer 1

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:

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

• Are PostgreSQL column names case-sensitive?

Exploiter