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

Commit3e803f7

Browse files
committed
Add "isodow" option to EXTRACT() and date_part() where Sunday = 7.
1 parent27d6ee0 commit3e803f7

File tree

6 files changed

+42
-13
lines changed

6 files changed

+42
-13
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 27 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.361 2007/02/16 07:46:54 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.362 2007/02/19 17:41:38 momjian Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -5732,16 +5732,16 @@ SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
57325732
<term><literal>dow</literal></term>
57335733
<listitem>
57345734
<para>
5735-
The day of the week(0 - 6;Sunday is 0) (for
5736-
<type>timestamp</type> values only)
5735+
The day of the weekasSunday(<literal>0</>) to
5736+
Saturday(<literal>6</>)
57375737
</para>
57385738

57395739
<screen>
57405740
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
57415741
<lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
57425742
</screen>
57435743
<para>
5744-
Note that <function>extract</function>'s day of the week numbering is
5744+
Note that <function>extract</function>'s day of the week numbering is
57455745
different from that of the <function>to_char</function> function.
57465746
</para>
57475747

@@ -5752,7 +5752,7 @@ SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
57525752
<term><literal>doy</literal></term>
57535753
<listitem>
57545754
<para>
5755-
The day of the year (1 - 365/366) (for <type>timestamp</type> values only)
5755+
The day of the year (1 - 365/366)
57565756
</para>
57575757

57585758
<screen>
@@ -5805,6 +5805,26 @@ SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
58055805
</listitem>
58065806
</varlistentry>
58075807

5808+
<varlistentry>
5809+
<term><literal>isodow</literal></term>
5810+
<listitem>
5811+
<para>
5812+
The day of the week as Monday(<literal>1</>) to
5813+
Sunday(<literal>7</>)
5814+
</para>
5815+
5816+
<screen>
5817+
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
5818+
<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
5819+
</screen>
5820+
<para>
5821+
This is identical to <literal>dow</> except for Sunday. This
5822+
matches the <acronym>ISO</> 8601 day of the week numbering.
5823+
</para>
5824+
5825+
</listitem>
5826+
</varlistentry>
5827+
58085828
<varlistentry>
58095829
<term><literal>isoyear</literal></term>
58105830
<listitem>
@@ -5923,8 +5943,7 @@ SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
59235943
<term><literal>quarter</literal></term>
59245944
<listitem>
59255945
<para>
5926-
The quarter of the year (1 - 4) that the day is in (for
5927-
<type>timestamp</type> values only)
5946+
The quarter of the year (1 - 4) that the day is in
59285947
</para>
59295948

59305949
<screen>
@@ -5989,7 +6008,7 @@ SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
59896008
(<acronym>ISO</acronym> 8601), the first week of a year
59906009
contains January 4 of that year. (The <acronym>ISO</acronym>-8601
59916010
week starts on Monday.) In other words, the first Thursday of
5992-
a year is in week 1 of that year. (for <type>timestamp</type> values only)
6011+
a year is in week 1 of that year.
59936012
</para>
59946013
<para>
59956014
Because of this, it is possible for early January dates to be part of the

‎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.176 2007/02/16 03:39:45 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.177 2007/02/19 17:41:39 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+
{"isodow",RESERV,DTK_ISODOW},/* ISO day of week, Sunday == 7 */
128129
{"isoyear",UNITS,DTK_ISOYEAR},/* year in terms of the ISO week date */
129130
{"j",UNITS,DTK_JULIAN},
130131
{"jan",MONTH,1},

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

Lines changed: 7 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/timestamp.c,v 1.172 2007/02/16 03:39:45 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.173 2007/02/19 17:41:39 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -4112,11 +4112,14 @@ timestamp_part(PG_FUNCTION_ARGS)
41124112
break;
41134113
}
41144114
caseDTK_DOW:
4115+
caseDTK_ISODOW:
41154116
if (timestamp2tm(timestamp,NULL,tm,&fsec,NULL,NULL)!=0)
41164117
ereport(ERROR,
41174118
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
41184119
errmsg("timestamp out of range")));
41194120
result=j2day(date2j(tm->tm_year,tm->tm_mon,tm->tm_mday));
4121+
if (val==DTK_ISODOW&&result==0)
4122+
result=7;
41204123
break;
41214124

41224125
caseDTK_DOY:
@@ -4322,11 +4325,14 @@ timestamptz_part(PG_FUNCTION_ARGS)
43224325
break;
43234326

43244327
caseDTK_DOW:
4328+
caseDTK_ISODOW:
43254329
if (timestamp2tm(timestamp,&tz,tm,&fsec,&tzn,NULL)!=0)
43264330
ereport(ERROR,
43274331
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
43284332
errmsg("timestamp out of range")));
43294333
result=j2day(date2j(tm->tm_year,tm->tm_mon,tm->tm_mday));
4334+
if (val==DTK_ISODOW&&result==0)
4335+
result=7;
43304336
break;
43314337

43324338
caseDTK_DOY:

‎src/include/utils/datetime.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
1010
* Portions Copyright (c) 1994, Regents of the University of California
1111
*
12-
* $PostgreSQL: pgsql/src/include/utils/datetime.h,v 1.64 2007/02/16 03:39:45 momjian Exp $
12+
* $PostgreSQL: pgsql/src/include/utils/datetime.h,v 1.65 2007/02/19 17:41:39 momjian Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -166,6 +166,7 @@
166166
#defineDTK_TZ_HOUR34
167167
#defineDTK_TZ_MINUTE35
168168
#defineDTK_ISOYEAR36
169+
#defineDTK_ISODOW37
169170

170171

171172
/*

‎src/interfaces/ecpg/pgtypeslib/dt.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/src/interfaces/ecpg/pgtypeslib/dt.h,v 1.35 2007/02/16 03:39:45 momjian Exp $ */
1+
/* $PostgreSQL: pgsql/src/interfaces/ecpg/pgtypeslib/dt.h,v 1.36 2007/02/19 17:41:39 momjian Exp $ */
22

33
#ifndefDT_H
44
#defineDT_H
@@ -158,6 +158,7 @@ typedef double fsec_t;
158158
#defineDTK_TZ_HOUR34
159159
#defineDTK_TZ_MINUTE35
160160
#defineDTK_ISOYEAR36
161+
#defineDTK_ISODOW37
161162

162163

163164
/*

‎src/interfaces/ecpg/pgtypeslib/dt_common.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/src/interfaces/ecpg/pgtypeslib/dt_common.c,v 1.36 2006/09/26 07:56:56 meskes Exp $ */
1+
/* $PostgreSQL: pgsql/src/interfaces/ecpg/pgtypeslib/dt_common.c,v 1.37 2007/02/19 17:41:39 momjian Exp $ */
22

33
#include"postgres_fe.h"
44

@@ -214,6 +214,7 @@ static datetkn datetktbl[] = {
214214
{"irkst",DTZ,POS(36)},/* Irkutsk Summer Time */
215215
{"irkt",TZ,POS(32)},/* Irkutsk Time */
216216
{"irt",TZ,POS(14)},/* Iran Time */
217+
{"isodow",RESERV,DTK_ISODOW},/* ISO day of week, Sunday == 7 */
217218
#if0
218219
isst
219220
#endif

0 commit comments

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