CODEWITHSUNDEEP
postgresqldateiso
RaphDG
RaphDG

Reputation: 1381

Date formatting with IYYY in PostgreSQL gives strange result

Date formatting with IYYY vs YYYY:

SELECT to_char( '2012-12-31'::DATE, 'IYYY-MM-DD' ) AS weird,
       to_char( '2012-12-31'::DATE, 'YYYY-MM-DD' ) AS expected;

Result:

   weird    |  expected  
------------+------------
 2013-12-31 | 2012-12-31

It would not be this surprised if the "weird" result was "2013-01-01".

What's wrong here ? (PostgreSQL 9.1.7)

Upvotes: 3

Views: 6207

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61636

IYYY refers to the year of the week of the specified date.

The first week of 2013 starts on Monday, 31 December 2012, so the weird result in the question is correct.

There's a warning directly related to this in the manual:

Attempting to construct a date using a mixture of ISO week and Gregorian date fields is nonsensical, and will cause an error. In the context of an ISO year, the concept of a "month" or "day of month" has no meaning. In the context of a Gregorian year, the ISO week has no meaning. Users should avoid mixing Gregorian and ISO date specifications.

See also wikipedia iso week date article for lots of details.

Upvotes: 8

Related Questions