PostgreSQL Date – Fix Incorrect Week Extraction at Year Start

postgresqlpostgresql-9.6

I have a query about extract(week from {date}) – when switching over to subsequent years, where I would expect it to return 1, it tends to return 53. To illustrate this point, consider the following bit of code:

with
    __steps as(
        select
            unnest(array['2015-12-20', '2015-12-27', '2016-01-03', '2016-01-10', '2016-01-17']::date[]) as step_from,
            unnest(array['2015-12-26', '2016-01-02', '2016-01-09', '2016-01-16', '2016-01-23']::date[]) as step_to
    )
select
    __steps.step_from,
    __steps.step_to,
    extract(week from __steps.step_from) as week
from
    __steps
order by
    __steps.step_from asc

This returns the following results

step_from   step_to     week
----------  ----------  ----
2015-12-20  2015-12-26  51
2015-12-27  2016-01-02  52
2016-01-03  2016-01-09  53
2016-01-10  2016-01-16  1
2016-01-17  2016-01-23  2

I would expect week starting Jan'03 to be week 1, but as you can see it's not. Is this an international standards thing? Or do I just have my Postgres not quite setup right?

show timezone
-------------
Europe/London

PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

Best Answer

I think I actually have an answer to this (typically mere minutes after asking my question), I was being severly thrown off by other extract() functionality.

2016-01-03 was a sunday. select extract(dow from '2016-01-03'::date) returns 0.

I was assuming that because extracting the day-of-week of a Sunday returns 0, then extract would consider weeks to start from Sunday, when in fact it considers weeks to start from Monday, as evidenced by this:

with
    __steps as(
        select unnest(array['2016-01-03', '2016-01-04']::date[]) as step_date
    )
select
    extract(week from step_date) as week,
    extract(dow from step_date) as dow,
    to_char(step_date, 'Day') as dow_name
from
    __steps
order by
    step_date asc

Returning

week    dow     dow_name
----    ---     --------
53      0       Sunday   
1       1       Monday
Related Question