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'?