Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4ebb0cf

Browse files
committed
Add two new format fields for use with to_char(), to_date() and
to_timestamp(): - ID for day-of-week - IDDD for day-of-yearThis makes it possible to convert ISO week dates to and from textfully 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
1 parentc7b0805 commit4ebb0cf

File tree

12 files changed

+773
-151
lines changed

12 files changed

+773
-151
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 55 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.358 2007/02/14 18:46:08 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.359 2007/02/16 03:39:44 momjian Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -4539,7 +4539,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
45394539
</row>
45404540
<row>
45414541
<entry><literal>I</literal></entry>
4542-
<entry>lastdigits of ISO year</entry>
4542+
<entry>lastdigit of ISO year</entry>
45434543
</row>
45444544
<row>
45454545
<entry><literal>BC</literal> or <literal>B.C.</literal> or
@@ -4607,6 +4607,10 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
46074607
<entry><literal>DDD</literal></entry>
46084608
<entry>day of year (001-366)</entry>
46094609
</row>
4610+
<row>
4611+
<entry><literal>IDDD</literal></entry>
4612+
<entry>ISO day of year (001-371; day 1 of the year is Monday of the first ISO week.)</entry>
4613+
</row>
46104614
<row>
46114615
<entry><literal>DD</literal></entry>
46124616
<entry>day of month (01-31)</entry>
@@ -4615,6 +4619,10 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
46154619
<entry><literal>D</literal></entry>
46164620
<entry>day of week (1-7; Sunday is 1)</entry>
46174621
</row>
4622+
<row>
4623+
<entry><literal>ID</literal></entry>
4624+
<entry>ISO day of week (1-7; Monday is 1)</entry>
4625+
</row>
46184626
<row>
46194627
<entry><literal>W</literal></entry>
46204628
<entry>week of month (1-5) (The first week starts on the first day of the month.)</entry>
@@ -4625,7 +4633,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
46254633
</row>
46264634
<row>
46274635
<entry><literal>IW</literal></entry>
4628-
<entry>ISO week number of year (The first Thursday of the new year is in week 1.)</entry>
4636+
<entry>ISO week number of year (1 - 53; the first Thursday of the new year is in week 1.)</entry>
46294637
</row>
46304638
<row>
46314639
<entry><literal>CC</literal></entry>
@@ -4791,6 +4799,27 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
47914799
</para>
47924800
</listitem>
47934801

4802+
<listitem>
4803+
<para>
4804+
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:
4805+
<itemizedlist>
4806+
<listitem>
4807+
<para>
4808+
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).
4809+
</para>
4810+
</listitem>
4811+
<listitem>
4812+
<para>
4813+
Year and day of year, for example <literal>to_date('2006-291', 'IYYY-IDDD')</literal> also returns <literal>2006-10-19</literal>.
4814+
</para>
4815+
</listitem>
4816+
</itemizedlist>
4817+
</para>
4818+
<para>
4819+
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.
4820+
</para>
4821+
</listitem>
4822+
47944823
<listitem>
47954824
<para>
47964825
Millisecond (<literal>MS</literal>) and microsecond (<literal>US</literal>)
@@ -5776,6 +5805,29 @@ SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
57765805
</listitem>
57775806
</varlistentry>
57785807

5808+
<varlistentry>
5809+
<term><literal>isoyear</literal></term>
5810+
<listitem>
5811+
<para>
5812+
The <acronym>ISO</acronym> 8601 year that the date falls in (not applicable to intervals).
5813+
</para>
5814+
5815+
<screen>
5816+
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
5817+
<lineannotation>Result: </lineannotation><computeroutput>2005</computeroutput>
5818+
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
5819+
<lineannotation>Result: </lineannotation><computeroutput>2006</computeroutput>
5820+
</screen>
5821+
5822+
<para>
5823+
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.
5824+
</para>
5825+
<para>
5826+
This field is not available in PostgreSQL releases prior to 8.3.
5827+
</para>
5828+
</listitem>
5829+
</varlistentry>
5830+
57795831
<varlistentry>
57805832
<term><literal>microseconds</literal></term>
57815833
<listitem>

‎src/backend/utils/adt/date.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/date.c,v 1.127 2007/01/05 22:19:40 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/date.c,v 1.128 2007/02/16 03:39:44 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1748,6 +1748,7 @@ time_part(PG_FUNCTION_ARGS)
17481748
caseDTK_DECADE:
17491749
caseDTK_CENTURY:
17501750
caseDTK_MILLENNIUM:
1751+
caseDTK_ISOYEAR:
17511752
default:
17521753
ereport(ERROR,
17531754
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

‎src/backend/utils/adt/datetime.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.175 2007/01/05 22:19:40 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.176 2007/02/16 03:39:45 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -125,6 +125,7 @@ static const datetkn datetktbl[] = {
125125
{"h",UNITS,DTK_HOUR},/* "hour" */
126126
{LATE,RESERV,DTK_LATE},/* "infinity" reserved for "late time" */
127127
{INVALID,RESERV,DTK_INVALID},/* "invalid" reserved for bad time */
128+
{"isoyear",UNITS,DTK_ISOYEAR},/* year in terms of the ISO week date */
128129
{"j",UNITS,DTK_JULIAN},
129130
{"jan",MONTH,1},
130131
{"january",MONTH,1},

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp