From 4ebb0cf9c30c1e477d5e2dfcc1f2c016c3f8bbcf Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Fri, 16 Feb 2007 03:39:46 +0000 Subject: Add two new format fields for use with to_char(), to_date() and to_timestamp(): - ID for day-of-week - IDDD for day-of-year This makes it possible to convert ISO week dates to and from text fully represented in either week ('IYYY-IW-ID') or day-of-year ('IYYY-IDDD') format. I have also added an 'isoyear' field for use with extract / date_part. Brendan Jurd --- doc/src/sgml/func.sgml | 58 +++++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 55 insertions(+), 3 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index ae9e9ec1058..76b150a9d22 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -4539,7 +4539,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); I - last digits of ISO year + last digit of ISO year BC or B.C. or @@ -4607,6 +4607,10 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); DDD day of year (001-366) + + IDDD + ISO day of year (001-371; day 1 of the year is Monday of the first ISO week.) + DD day of month (01-31) @@ -4615,6 +4619,10 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); D day of week (1-7; Sunday is 1) + + ID + ISO day of week (1-7; Monday is 1) + W week of month (1-5) (The first week starts on the first day of the month.) @@ -4625,7 +4633,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); IW - ISO week number of year (The first Thursday of the new year is in week 1.) + ISO week number of year (1 - 53; the first Thursday of the new year is in week 1.) CC @@ -4791,6 +4799,27 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); + + + An ISO week date (as distinct from a Gregorian date) can be specified to to_timestamp and to_date in one of two ways: + + + + Year, week and weekday, for example to_date('2006-42-4', 'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it is assumed to be 1 (Monday). + + + + + Year and day of year, for example to_date('2006-291', 'IYYY-IDDD') also returns 2006-10-19. + + + + + + Attempting to construct a date using a mixture of ISO week and Gregorian date fields is nonsensical, and could yield unexpected results. 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 take care to keep Gregorian and ISO date specifications separate. + + + Millisecond (MS) and microsecond (US) @@ -5776,6 +5805,29 @@ SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); + + isoyear + + + The ISO 8601 year that the date falls in (not applicable to intervals). + + + +SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01'); +Result: 2005 +SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); +Result: 2006 + + + + Each ISO year begins with the Monday of the week containing the 4th of January, so in early January or late December the ISO year may be different from the Gregorian year. See the week field for more information. + + + This field is not available in PostgreSQL releases prior to 8.3. + + + + microseconds -- cgit v1.2.3