🌐 AI搜索 & 代理 主页
Skip to content

Commit 3e803f7

Browse files
bmomjianbmomjian
committed
Add "isodow" option to EXTRACT() and date_part() where Sunday = 7.
1 parent 27d6ee0 commit 3e803f7

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 week as Sunday(<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
case DTK_DOW:
4115+
case DTK_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
case DTK_DOY:
@@ -4322,11 +4325,14 @@ timestamptz_part(PG_FUNCTION_ARGS)
43224325
break;
43234326

43244327
case DTK_DOW:
4328+
case DTK_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
case DTK_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
#define DTK_TZ_HOUR 34
167167
#define DTK_TZ_MINUTE 35
168168
#define DTK_ISOYEAR 36
169+
#define DTK_ISODOW 37
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
#ifndef DT_H
44
#define DT_H
@@ -158,6 +158,7 @@ typedef double fsec_t;
158158
#define DTK_TZ_HOUR 34
159159
#define DTK_TZ_MINUTE 35
160160
#define DTK_ISOYEAR 36
161+
#define DTK_ISODOW 37
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
#if 0
218219
isst
219220
#endif

0 commit comments

Comments
 (0)