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

Commit9a54b76

Browse files
committed
Fix handling of CC (century) format spec in to_date/to_char. According to
standard convention the 21st century runs from 2001-2100, not 2000-2099,so make it work like that. Per bug #2885 from Akio Iwaasa.Backpatch to 8.2, but no further, since this is really a definitionalchange; users of older branches are probably more interested in stability.
1 parent113fbe1 commit9a54b76

File tree

4 files changed

+51
-48
lines changed

4 files changed

+51
-48
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.349 2007/01/1220:06:10 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.350 2007/01/1223:34:54 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -4617,7 +4617,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
46174617
</row>
46184618
<row>
46194619
<entry><literal>CC</literal></entry>
4620-
<entry>centurycomponent of year (minimum 2 digits)</entry>
4620+
<entry>century(2 digits) (The twenty-first century starts on 2001-01-01.)</entry>
46214621
</row>
46224622
<row>
46234623
<entry><literal>J</literal></entry>

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

Lines changed: 21 additions & 18 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.117 2007/01/05 22:19:40 momjian Exp $
4+
* $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.118 2007/01/12 23:34:54 tgl Exp $
55
*
66
*
77
* Portions Copyright (c) 1999-2007, PostgreSQL Global Development Group
@@ -2445,7 +2445,10 @@ dch_date(int arg, char *inout, int suf, bool is_to_char, bool is_interval,
24452445
caseDCH_CC:
24462446
if (is_to_char)
24472447
{
2448-
i=tm->tm_year /100+ ((is_interval) ?0 :1);
2448+
if (is_interval)/* straight calculation */
2449+
i=tm->tm_year /100;
2450+
else/* century 21 starts in 2001 */
2451+
i= (tm->tm_year-1) /100+1;
24492452
if (i <=99&&i >=-99)
24502453
sprintf(inout,"%0*d",S_FM(suf) ?0 :2,i);
24512454
else
@@ -3295,9 +3298,6 @@ do_to_timestamp(text *date_txt, text *fmt,
32953298
tm->tm_sec=x;
32963299
}
32973300

3298-
if (tmfc.cc)
3299-
tm->tm_year= (tmfc.cc-1)*100;
3300-
33013301
if (tmfc.ww)
33023302
tmfc.ddd= (tmfc.ww-1)*7+1;
33033303

@@ -3347,24 +3347,27 @@ do_to_timestamp(text *date_txt, text *fmt,
33473347

33483348
if (tmfc.year)
33493349
{
3350-
if (tmfc.yysz==2&&tmfc.cc)
3351-
{
3352-
/*
3353-
*CC and YY defined why -[2000|1900]? See dch_date() DCH_YY code.
3354-
*/
3355-
tm->tm_year= (tmfc.cc-1)*100+ (tmfc.year>=2000 ?tmfc.year-2000 :tmfc.year-1900);
3356-
}
3357-
elseif (tmfc.yysz==1&&tmfc.cc)
3350+
/*
3351+
* If CC and YY (or Y) are provided, use YY as 2 low-order digits
3352+
* for the year in the given century. Keep in mind that the 21st
3353+
*century runs from 2001-2100, not 2000-2099.
3354+
*
3355+
* If a 4-digityearis provided, we use that and ignore CC.
3356+
*/
3357+
if (tmfc.cc&&tmfc.yysz <=2)
33583358
{
3359-
/*
3360-
* CC and Y defined
3361-
*/
3362-
tm->tm_year= (tmfc.cc-1)*100+tmfc.year-2000;
3359+
tm->tm_year=tmfc.year %100;
3360+
if (tm->tm_year)
3361+
tm->tm_year+= (tmfc.cc-1)*100;
3362+
else
3363+
tm->tm_year=tmfc.cc*100;
33633364
}
33643365
else
3365-
/* set year (and ignore CC if defined) */
33663366
tm->tm_year=tmfc.year;
33673367
}
3368+
elseif (tmfc.cc)/* use first year of century */
3369+
tm->tm_year= (tmfc.cc-1)*100+1;
3370+
33683371
if (tmfc.bc)
33693372
{
33703373
if (tm->tm_year>0)

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

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -867,11 +867,11 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
867867
| 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
868868
| 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
869869
| 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
870-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
871-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
872-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
873-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
874-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
870+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
871+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
872+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
873+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
874+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
875875
| 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
876876
| 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
877877
| 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
@@ -912,8 +912,8 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
912912
| 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
913913
| 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
914914
| 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
915-
| 2,000 2000 000 00 021 1 01 01 001 01 7 2451545
916-
| 2,000 2000 000 00 021 4 12 53 366 31 1 2451910
915+
| 2,000 2000 000 00 020 1 01 01 001 01 7 2451545
916+
| 2,000 2000 000 00 020 4 12 53 366 31 1 2451910
917917
| 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
918918
(65 rows)
919919

@@ -938,11 +938,11 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
938938
| 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
939939
| 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
940940
| 2,001 2001 001 01 1 21 3 9 38 265 22 7 2452175
941-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
942-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
943-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
944-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
945-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
941+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
942+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
943+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
944+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
945+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
946946
| 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
947947
| 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
948948
| 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
@@ -983,8 +983,8 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
983983
| 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
984984
| 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
985985
| 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
986-
| 2,000 2000 000 00 021 1 1 1 1 1 7 2451545
987-
| 2,000 2000 000 00 021 4 12 53 366 31 1 2451910
986+
| 2,000 2000 000 00 020 1 1 1 1 1 7 2451545
987+
| 2,000 2000 000 00 020 4 12 53 366 31 1 2451910
988988
| 2,001 2001 001 01 1 21 1 1 1 1 1 2 2451911
989989
(65 rows)
990990

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

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -887,11 +887,11 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
887887
| 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
888888
| 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
889889
| 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
890-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
891-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
892-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
893-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
894-
| 2,000 2000 000 00 021 1 03 11 075 15 4 2451619
890+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
891+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
892+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
893+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
894+
| 2,000 2000 000 00 020 1 03 11 075 15 4 2451619
895895
| 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
896896
| 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
897897
| 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
@@ -933,8 +933,8 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
933933
| 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
934934
| 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
935935
| 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
936-
| 2,000 2000 000 00 021 1 01 01 001 01 7 2451545
937-
| 2,000 2000 000 00 021 4 12 53 366 31 1 2451910
936+
| 2,000 2000 000 00 020 1 01 01 001 01 7 2451545
937+
| 2,000 2000 000 00 020 4 12 53 366 31 1 2451910
938938
| 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
939939
(66 rows)
940940

@@ -960,11 +960,11 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
960960
| 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
961961
| 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
962962
| 2,001 2001 001 01 1 21 3 9 38 265 22 7 2452175
963-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
964-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
965-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
966-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
967-
| 2,000 2000 000 00 021 1 3 11 75 15 4 2451619
963+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
964+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
965+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
966+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
967+
| 2,000 2000 000 00 020 1 3 11 75 15 4 2451619
968968
| 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
969969
| 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
970970
| 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
@@ -1006,8 +1006,8 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
10061006
| 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
10071007
| 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
10081008
| 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1009-
| 2,000 2000 000 00 021 1 1 1 1 1 7 2451545
1010-
| 2,000 2000 000 00 021 4 12 53 366 31 1 2451910
1009+
| 2,000 2000 000 00 020 1 1 1 1 1 7 2451545
1010+
| 2,000 2000 000 00 020 4 12 53 366 31 1 2451910
10111011
| 2,001 2001 001 01 1 21 1 1 1 1 1 2 2451911
10121012
(66 rows)
10131013

0 commit comments

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