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

Commit1c757c4

Browse files
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 parent03caf76 commit1c757c4

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
caseDCH_YYYY:
2466+
caseDCH_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
returnstrlen(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
caseDCH_YYY:
2505+
caseDCH_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
caseDCH_YY:
2540+
caseDCH_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
caseDCH_Y:
2575+
caseDCH_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(intyear,intmon,intmday)
2929+
{
2930+
float8result;
2931+
intday0,
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+
returnyear;
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
externvoidisoweek2date(intwoy,int*year,int*mon,int*mday);
250250
externintdate2isoweek(intyear,intmon,intmday);
251+
externintdate2isoyear(intyear,intmon,intmday);
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)
[8]ページ先頭
©2009-2025 Movatter.jp