Skip to content

Commit 1c757c4

Browse files
bmomjianbmomjian
committed
> > I have no idea if this in Oracle or not. But it's something I
> > needed, and other people in the past asked about it too. > > It is in Oracle, but you aren't exactly on the spot. It should be > > IYYY - 4 digits ('2003') > IYY - 3 digits ('003') > IY - 2 digits ('03') > I - 1 digit ('3') Here is an updated patch that does that. Kurt Roeckx
1 parent 03caf76 commit 1c757c4

File tree

6 files changed

+220
-10
lines changed

6 files changed

+220
-10
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.183 2003/12/18 03:59:07 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.184 2003/12/25 03:36:23 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -3983,6 +3983,22 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
39833983
<entry><literal>Y</literal></entry>
39843984
<entry>last digit of year</entry>
39853985
</row>
3986+
<row>
3987+
<entry><literal>IYYY</literal></entry>
3988+
<entry>ISO year (4 and more digits)</entry>
3989+
</row>
3990+
<row>
3991+
<entry><literal>IYY</literal></entry>
3992+
<entry>last 3 digits of ISO year</entry>
3993+
</row>
3994+
<row>
3995+
<entry><literal>IY</literal></entry>
3996+
<entry>last 2 digits of ISO year</entry>
3997+
</row>
3998+
<row>
3999+
<entry><literal>I</literal></entry>
4000+
<entry>last digits of ISO year</entry>
4001+
</row>
39864002
<row>
39874003
<entry><literal>BC</literal> or <literal>B.C.</literal> or
39884004
<literal>AD</literal> or <literal>A.D.</literal></entry>

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

Lines changed: 51 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/* -----------------------------------------------------------------------
22
* formatting.c
33
*
4-
* $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.70 2003/11/29 19:51:58 pgsql Exp $
4+
* $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.71 2003/12/25 03:36:23 momjian Exp $
55
*
66
*
77
* Portions Copyright (c) 1999-2003, PostgreSQL Global Development Group
@@ -525,6 +525,10 @@ typedef enum
525525
DCH_HH12,
526526
DCH_HH,
527527
DCH_IW,
528+
DCH_IYYY,
529+
DCH_IYY,
530+
DCH_IY,
531+
DCH_I,
528532
DCH_J,
529533
DCH_MI,
530534
DCH_MM,
@@ -565,6 +569,10 @@ typedef enum
565569
DCH_hh12,
566570
DCH_hh,
567571
DCH_iw,
572+
DCH_iyyy,
573+
DCH_iyy,
574+
DCH_iy,
575+
DCH_i,
568576
DCH_j,
569577
DCH_mi,
570578
DCH_mm,
@@ -659,6 +667,10 @@ static KeyWord DCH_keywords[] = {
659667
{"HH12", 4, dch_time, DCH_HH12, TRUE},
660668
{"HH", 2, dch_time, DCH_HH, TRUE},
661669
{"IW", 2, dch_date, DCH_IW, TRUE}, /* I */
670+
{"IYYY", 4, dch_date, DCH_IYYY, TRUE},
671+
{"IYY", 3, dch_date, DCH_IYY, TRUE},
672+
{"IY", 2, dch_date, DCH_IY, TRUE},
673+
{"I", 1, dch_date, DCH_I, TRUE},
662674
{"J", 1, dch_date, DCH_J, TRUE}, /* J */
663675
{"MI", 2, dch_time, DCH_MI, TRUE},
664676
{"MM", 2, dch_date, DCH_MM, TRUE},
@@ -699,6 +711,10 @@ static KeyWord DCH_keywords[] = {
699711
{"hh12", 4, dch_time, DCH_HH12, TRUE},
700712
{"hh", 2, dch_time, DCH_HH, TRUE},
701713
{"iw", 2, dch_date, DCH_IW, TRUE}, /* i */
714+
{"iyyy", 4, dch_date, DCH_IYYY, TRUE},
715+
{"iyy", 3, dch_date, DCH_IYY, TRUE},
716+
{"iy", 2, dch_date, DCH_IY, TRUE},
717+
{"i", 1, dch_date, DCH_I, TRUE},
702718
{"j", 1, dch_time, DCH_J, TRUE}, /* j */
703719
{"mi", 2, dch_time, DCH_MI, TRUE}, /* m */
704720
{"mm", 2, dch_date, DCH_MM, TRUE},
@@ -2447,12 +2463,26 @@ dch_date(int arg, char *inout, int suf, int flag, FormatNode *node, void *data)
24472463
}
24482464
break;
24492465
case DCH_YYYY:
2466+
case DCH_IYYY:
24502467
if (flag == TO_CHAR)
24512468
{
24522469
if (tm->tm_year <= 9999 && tm->tm_year >= -9998)
2453-
sprintf(inout, "%0*d", S_FM(suf) ? 0 : 4, YEAR_ABS(tm->tm_year));
2470+
sprintf(inout, "%0*d",
2471+
S_FM(suf) ? 0 : 4,
2472+
arg == DCH_YYYY ?
2473+
YEAR_ABS(tm->tm_year) :
2474+
YEAR_ABS(date2isoyear(
2475+
tm->tm_year,
2476+
tm->tm_mon,
2477+
tm->tm_mday)));
24542478
else
2455-
sprintf(inout, "%d", YEAR_ABS(tm->tm_year));
2479+
sprintf(inout, "%d",
2480+
arg == DCH_YYYY ?
2481+
YEAR_ABS(tm->tm_year) :
2482+
YEAR_ABS(date2isoyear(
2483+
tm->tm_year,
2484+
tm->tm_mon,
2485+
tm->tm_mday)));
24562486
if (S_THth(suf))
24572487
str_numth(p_inout, inout, S_TH_TYPE(suf));
24582488
return strlen(p_inout) - 1;
@@ -2472,9 +2502,14 @@ dch_date(int arg, char *inout, int suf, int flag, FormatNode *node, void *data)
24722502
}
24732503
break;
24742504
case DCH_YYY:
2505+
case DCH_IYY:
24752506
if (flag == TO_CHAR)
24762507
{
2477-
snprintf(buff, sizeof(buff), "%03d", YEAR_ABS(tm->tm_year));
2508+
snprintf(buff, sizeof(buff), "%03d",
2509+
arg == DCH_YYY ?
2510+
YEAR_ABS(tm->tm_year) :
2511+
YEAR_ABS(date2isoyear(tm->tm_year,
2512+
tm->tm_mon, tm->tm_mday)));
24782513
i = strlen(buff);
24792514
strcpy(inout, buff + (i - 3));
24802515
if (S_THth(suf))
@@ -2502,9 +2537,14 @@ dch_date(int arg, char *inout, int suf, int flag, FormatNode *node, void *data)
25022537
}
25032538
break;
25042539
case DCH_YY:
2540+
case DCH_IY:
25052541
if (flag == TO_CHAR)
25062542
{
2507-
snprintf(buff, sizeof(buff), "%02d", YEAR_ABS(tm->tm_year));
2543+
snprintf(buff, sizeof(buff), "%02d",
2544+
arg == DCH_YY ?
2545+
YEAR_ABS(tm->tm_year) :
2546+
YEAR_ABS(date2isoyear(tm->tm_year,
2547+
tm->tm_mon, tm->tm_mday)));
25082548
i = strlen(buff);
25092549
strcpy(inout, buff + (i - 2));
25102550
if (S_THth(suf))
@@ -2532,9 +2572,14 @@ dch_date(int arg, char *inout, int suf, int flag, FormatNode *node, void *data)
25322572
}
25332573
break;
25342574
case DCH_Y:
2575+
case DCH_I:
25352576
if (flag == TO_CHAR)
25362577
{
2537-
snprintf(buff, sizeof(buff), "%1d", YEAR_ABS(tm->tm_year));
2578+
snprintf(buff, sizeof(buff), "%1d",
2579+
arg == DCH_Y ?
2580+
YEAR_ABS(tm->tm_year) :
2581+
YEAR_ABS(date2isoyear(tm->tm_year,
2582+
tm->tm_mon, tm->tm_mday)));
25382583
i = strlen(buff);
25392584
strcpy(inout, buff + (i - 1));
25402585
if (S_THth(suf))

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

Lines changed: 60 additions & 2 deletions
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.97 2003/11/29 19:51:59 pgsql Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.98 2003/12/25 03:36:23 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2840,7 +2840,7 @@ interval_trunc(PG_FUNCTION_ARGS)
28402840

28412841
/* isoweek2date()
28422842
* Convert ISO week of year number to date.
2843-
* The year field must be specified!
2843+
* The year field must be specified with the ISO year!
28442844
* karel 2000/08/07
28452845
*/
28462846
void
@@ -2920,6 +2920,64 @@ date2isoweek(int year, int mon, int mday)
29202920
}
29212921

29222922

2923+
/* date2isoyear()
2924+
*
2925+
* Returns ISO 8601 year number.
2926+
*/
2927+
int
2928+
date2isoyear(int year, int mon, int mday)
2929+
{
2930+
float8 result;
2931+
int day0,
2932+
day4,
2933+
dayn;
2934+
2935+
/* current day */
2936+
dayn = date2j(year, mon, mday);
2937+
2938+
/* fourth day of current year */
2939+
day4 = date2j(year, 1, 4);
2940+
2941+
/* day0 == offset to first day of week (Monday) */
2942+
day0 = j2day(day4 - 1);
2943+
2944+
/*
2945+
* We need the first week containing a Thursday, otherwise this day
2946+
* falls into the previous year for purposes of counting weeks
2947+
*/
2948+
if (dayn < (day4 - day0))
2949+
{
2950+
day4 = date2j(year - 1, 1, 4);
2951+
2952+
/* day0 == offset to first day of week (Monday) */
2953+
day0 = j2day(day4 - 1);
2954+
2955+
year--;
2956+
}
2957+
2958+
result = (((dayn - (day4 - day0)) / 7) + 1);
2959+
2960+
/*
2961+
* Sometimes the last few days in a year will fall into the first week
2962+
* of the next year, so check for this.
2963+
*/
2964+
if (result >= 53)
2965+
{
2966+
day4 = date2j(year + 1, 1, 4);
2967+
2968+
/* day0 == offset to first day of week (Monday) */
2969+
day0 = j2day(day4 - 1);
2970+
2971+
if (dayn >= (day4 - day0))
2972+
{
2973+
year++;
2974+
}
2975+
}
2976+
2977+
return year;
2978+
}
2979+
2980+
29232981
/* timestamp_part()
29242982
* Extract specified field from timestamp.
29252983
*/

‎src/include/utils/timestamp.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.32 2003/11/29 22:41:16 pgsql Exp $
9+
* $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.33 2003/12/25 03:36:24 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -248,5 +248,6 @@ extern void GetEpochTime(struct tm * tm);
248248

249249
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
250250
extern int date2isoweek(int year, int mon, int mday);
251+
extern int date2isoyear(int year, int mon, int mday);
251252

252253
#endif /* TIMESTAMP_H */

‎src/test/regress/expected/timestamptz.out

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1317,6 +1317,76 @@ SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. H
13171317
| 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
13181318
(64 rows)
13191319

1320+
SELECT '' AS to_char_10, to_char(d1, 'YYYY WW IYYY IYY IY I IW')
1321+
FROM TIMESTAMPTZ_TBL;
1322+
to_char_10 | to_char
1323+
------------+--------------------------
1324+
|
1325+
|
1326+
| 1969 53 1970 970 70 0 01
1327+
| 1997 06 1997 997 97 7 07
1328+
| 1997 06 1997 997 97 7 07
1329+
| 1997 06 1997 997 97 7 07
1330+
| 1997 06 1997 997 97 7 07
1331+
| 1997 06 1997 997 97 7 07
1332+
| 1997 06 1997 997 97 7 07
1333+
| 1997 01 1997 997 97 7 01
1334+
| 1997 01 1997 997 97 7 01
1335+
| 1997 06 1997 997 97 7 07
1336+
| 1997 06 1997 997 97 7 07
1337+
| 1997 06 1997 997 97 7 07
1338+
| 1997 06 1997 997 97 7 07
1339+
| 1997 23 1997 997 97 7 24
1340+
| 2001 38 2001 001 01 1 38
1341+
| 2000 11 2000 000 00 0 11
1342+
| 2000 11 2000 000 00 0 11
1343+
| 2000 11 2000 000 00 0 11
1344+
| 2000 11 2000 000 00 0 11
1345+
| 2000 11 2000 000 00 0 11
1346+
| 1997 06 1997 997 97 7 07
1347+
| 1997 06 1997 997 97 7 07
1348+
| 1997 06 1997 997 97 7 07
1349+
| 1997 06 1997 997 97 7 07
1350+
| 1997 06 1997 997 97 7 07
1351+
| 1997 06 1997 997 97 7 07
1352+
| 1997 06 1997 997 97 7 07
1353+
| 1997 06 1997 997 97 7 07
1354+
| 1997 06 1997 997 97 7 07
1355+
| 1997 06 1997 997 97 7 07
1356+
| 1997 06 1997 997 97 7 07
1357+
| 1997 23 1997 997 97 7 24
1358+
| 1997 06 1997 997 97 7 07
1359+
| 1997 06 1997 997 97 7 07
1360+
| 1997 07 1997 997 97 7 07
1361+
| 1997 07 1997 997 97 7 07
1362+
| 1997 07 1997 997 97 7 07
1363+
| 1997 07 1997 997 97 7 07
1364+
| 1997 07 1997 997 97 7 07
1365+
| 0097 07 0097 097 97 7 07
1366+
| 0097 07 0097 097 97 7 07
1367+
| 0597 07 0597 597 97 7 07
1368+
| 1097 07 1097 097 97 7 07
1369+
| 1697 07 1697 697 97 7 07
1370+
| 1797 07 1797 797 97 7 07
1371+
| 1897 07 1897 897 97 7 07
1372+
| 1997 07 1997 997 97 7 07
1373+
| 2097 07 2097 097 97 7 07
1374+
| 1996 09 1996 996 96 6 09
1375+
| 1996 09 1996 996 96 6 09
1376+
| 1996 09 1996 996 96 6 09
1377+
| 1996 53 1997 997 97 7 01
1378+
| 1996 53 1997 997 97 7 01
1379+
| 1997 01 1997 997 97 7 01
1380+
| 1997 09 1997 997 97 7 09
1381+
| 1997 09 1997 997 97 7 09
1382+
| 1997 52 1998 998 98 8 01
1383+
| 1997 53 1998 998 98 8 01
1384+
| 1999 53 1999 999 99 9 52
1385+
| 2000 01 1999 999 99 9 52
1386+
| 2000 53 2000 000 00 0 52
1387+
| 2001 01 2001 001 01 1 01
1388+
(64 rows)
1389+
13201390
-- TO_TIMESTAMP()
13211391
SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
13221392
to_timestamp_1 | to_timestamp
@@ -1402,4 +1472,16 @@ SELECT '' AS to_timestamp_14, to_timestamp('995-1116', 'YYY-MMDD');
14021472
| Thu Nov 16 00:00:00 1995 PST
14031473
(1 row)
14041474

1475+
SELECT '' AS to_timestamp_15, to_timestamp('200401', 'IYYYIW');
1476+
to_timestamp_15 | to_timestamp
1477+
-----------------+------------------------------
1478+
| Mon Dec 29 00:00:00 2003 PST
1479+
(1 row)
1480+
1481+
SELECT '' AS to_timestamp_16, to_timestamp('200401', 'YYYYWW');
1482+
to_timestamp_16 | to_timestamp
1483+
-----------------+------------------------------
1484+
| Thu Jan 01 00:00:00 2004 PST
1485+
(1 row)
1486+
14051487
SET DateStyle TO DEFAULT;

‎src/test/regress/sql/timestamptz.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -189,6 +189,9 @@ SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth')
189189
SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm')
190190
FROM TIMESTAMPTZ_TBL;
191191

192+
SELECT '' AS to_char_10, to_char(d1, 'YYYY WW IYYY IYY IY I IW')
193+
FROM TIMESTAMPTZ_TBL;
194+
192195
-- TO_TIMESTAMP()
193196
SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
194197

@@ -220,4 +223,9 @@ SELECT '' AS to_timestamp_13, to_timestamp('95-1116', 'YY-MMDD');
220223

221224
SELECT '' AS to_timestamp_14, to_timestamp('995-1116', 'YYY-MMDD');
222225

226+
SELECT '' AS to_timestamp_15, to_timestamp('200401', 'IYYYIW');
227+
228+
SELECT '' AS to_timestamp_16, to_timestamp('200401', 'YYYYWW');
229+
230+
223231
SET DateStyle TO DEFAULT;

0 commit comments

Comments
 (0)