diff options
Diffstat (limited to 'doc/src/sgml/func.sgml')
| -rw-r--r-- | doc/src/sgml/func.sgml | 58 |
1 files changed, 55 insertions, 3 deletions
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 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.358 2007/02/14 18:46:08 petere Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.359 2007/02/16 03:39:44 momjian Exp $ --> <chapter id="functions"> <title>Functions and Operators</title> @@ -4539,7 +4539,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); </row> <row> <entry><literal>I</literal></entry> - <entry>last digits of ISO year</entry> + <entry>last digit of ISO year</entry> </row> <row> <entry><literal>BC</literal> or <literal>B.C.</literal> or @@ -4608,6 +4608,10 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); <entry>day of year (001-366)</entry> </row> <row> + <entry><literal>IDDD</literal></entry> + <entry>ISO day of year (001-371; day 1 of the year is Monday of the first ISO week.)</entry> + </row> + <row> <entry><literal>DD</literal></entry> <entry>day of month (01-31)</entry> </row> @@ -4616,6 +4620,10 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); <entry>day of week (1-7; Sunday is 1)</entry> </row> <row> + <entry><literal>ID</literal></entry> + <entry>ISO day of week (1-7; Monday is 1)</entry> + </row> + <row> <entry><literal>W</literal></entry> <entry>week of month (1-5) (The first week starts on the first day of the month.)</entry> </row> @@ -4625,7 +4633,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); </row> <row> <entry><literal>IW</literal></entry> - <entry>ISO week number of year (The first Thursday of the new year is in week 1.)</entry> + <entry>ISO week number of year (1 - 53; the first Thursday of the new year is in week 1.)</entry> </row> <row> <entry><literal>CC</literal></entry> @@ -4793,6 +4801,27 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); <listitem> <para> + An ISO week date (as distinct from a Gregorian date) can be specified to <function>to_timestamp</function> and <function>to_date</function> in one of two ways: + <itemizedlist> + <listitem> + <para> + Year, week and weekday, for example <literal>to_date('2006-42-4', 'IYYY-IW-ID')</literal> returns the date <literal>2006-10-19</literal>. If you omit the weekday it is assumed to be 1 (Monday). + </para> + </listitem> + <listitem> + <para> + Year and day of year, for example <literal>to_date('2006-291', 'IYYY-IDDD')</literal> also returns <literal>2006-10-19</literal>. + </para> + </listitem> + </itemizedlist> + </para> + <para> + 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. + </para> + </listitem> + + <listitem> + <para> Millisecond (<literal>MS</literal>) and microsecond (<literal>US</literal>) values in a conversion from string to <type>timestamp</type> are used as part of the seconds after the decimal point. For example @@ -5777,6 +5806,29 @@ SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); </varlistentry> <varlistentry> + <term><literal>isoyear</literal></term> + <listitem> + <para> + The <acronym>ISO</acronym> 8601 year that the date falls in (not applicable to intervals). + </para> + +<screen> +SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01'); +<lineannotation>Result: </lineannotation><computeroutput>2005</computeroutput> +SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); +<lineannotation>Result: </lineannotation><computeroutput>2006</computeroutput> +</screen> + + <para> + Each <acronym>ISO</acronym> year begins with the Monday of the week containing the 4th of January, so in early January or late December the <acronym>ISO</acronym> year may be different from the Gregorian year. See the <literal>week</literal> field for more information. + </para> + <para> + This field is not available in PostgreSQL releases prior to 8.3. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>microseconds</literal></term> <listitem> <para> |