0

I've been doing some statistics analysis of data in a Postgres (v10.6) database grouped by ISO week and have stumbled upon an apparent inconsistency in how week numbers are returned, for instance:

SELECT TO_CHAR(DATE_TRUNC('week',TIMESTAMP '2019-02-14'),'WW');

returns 06

whereas:

SELECT EXTRACT(WEEK FROM TIMESTAMP '2019-02-14');

returns 7

Why is there a discrepancy / which one of these is correct ? I suspect the latter, as the equivalent operation in PHP returns 07 too:

$dte = new DateTime('2019-02-14');
echo $dte->format('W');

I'm mostly concerned that there is some good reason for this difference that I'm missing ...

Almetraet
  • 41
  • 5

1 Answers1

3

As documented in the manual 'WW' returns

week number of year (1-53) (the first week starts on the first day of the year)

(emphasis mine)

extract(week ...) always returns the ISO week. If you want to_char() to also return the ISO week, you need to use 'IW' instead. If you also want to display the year with to_char() you should use 'IYYY' instead of 'YYYY' which is equivalent to extract(isoyear ...)