Search Postgresql Archives

ISO week dates

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hey guys,

I have a question regarding the ISO 8601 week date format.  Outputting dates in this format seems to be partially supported, and rather inconsistent.  The documentation for to_char() lists 'IYYY' (ISO year) and 'IW' (ISO week) as format patterns, but there is no "ISO day of week" format pattern to complete the set.

A full ISO week date is written as "<year>-W<week>-<day>", where <day> is the day of week with Monday = 1 and Sunday = 7.

The format pattern 'D' does not help, since it numbers weekdays beginning at Sunday = 1.

You could use the extract() function instead, but again, support is partial and inconsistent.  You can get the right day of week by using the 'dow' field and adding one, the 'week' field returns the ISO week, but the 'year' field returns the Gregorian year!

So to_char() has the ISO year and week, but not the day.  extract() has ISO day (sort of) and week, but not the year.

Granted you can put a working ISO format together by using both functions; something like

create function to_iso(timestamp) returns text as $$
 SELECT to_char('IYYY', $1) || '-W' || extract(week, $1) || '-' || (extract(dow, $1) + 1)
$$ language sql immutable;

... but this seems unnecessarily awkward.  Why not:

 * add an ISO day format pattern to to_char() called 'ID', which starts at Monday = 1, and
 * add an ISO year field to extract() called 'isoyear'?

Regards,
BJ

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux