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

Commitac78c41

Browse files
committed
Fix to_char(), to_date(), and to_timestamp() to handle negative/BC
century specifications just like positive/AD centuries. Previously thebehavior was either wrong or inconsistent with positive/AD handling.Centuries without years now always assume the first year of the century,which is now documented.
1 parentfbcfa90 commitac78c41

File tree

4 files changed

+39
-22
lines changed

4 files changed

+39
-22
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5575,7 +5575,9 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
55755575
if there is a <literal>YYY</literal>, <literal>YYYY</literal> or
55765576
<literal>Y,YYY</literal> field. If <literal>CC</literal> is used with
55775577
<literal>YY</literal> or <literal>Y</literal> then the year is computed
5578-
as <literal>(CC-1)*100+YY</literal>.
5578+
as the year in the specified century. If the century is
5579+
specified but the year is not, the first year of the century
5580+
is assumed.
55795581
</para>
55805582
</listitem>
55815583

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

Lines changed: 32 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -2640,8 +2640,15 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
26402640
caseDCH_CC:
26412641
if (is_interval)/* straight calculation */
26422642
i=tm->tm_year /100;
2643-
else/* century 21 starts in 2001 */
2644-
i= (tm->tm_year-1) /100+1;
2643+
else
2644+
{
2645+
if (tm->tm_year>0)
2646+
/* Century 20 == 1901 - 2000 */
2647+
i= (tm->tm_year-1) /100+1;
2648+
else
2649+
/* Century 6BC == 600BC - 501BC */
2650+
i=tm->tm_year /100-1;
2651+
}
26452652
if (i <=99&&i >=-99)
26462653
sprintf(s,"%0*d",S_FM(n->suffix) ?0 :2,i);
26472654
else
@@ -3465,33 +3472,41 @@ do_to_timestamp(text *date_txt, text *fmt,
34653472
/*
34663473
* If CC and YY (or Y) are provided, use YY as 2 low-order digits for
34673474
* the year in the given century. Keep in mind that the 21st century
3468-
* runs from 2001-2100, not 2000-2099.
3469-
*
3470-
* If a 4-digit year is provided, we use that and ignore CC.
3475+
* AD runs from 2001-2100, not 2000-2099; 6th century BC runs from
3476+
* 600BC to 501BC.
34713477
*/
34723478
if (tmfc.cc&&tmfc.yysz <=2)
34733479
{
3480+
if (tmfc.bc)
3481+
tmfc.cc=-tmfc.cc;
34743482
tm->tm_year=tmfc.year %100;
34753483
if (tm->tm_year)
3476-
tm->tm_year+= (tmfc.cc-1)*100;
3484+
{
3485+
if (tmfc.cc >=0)
3486+
tm->tm_year+= (tmfc.cc-1)*100;
3487+
else
3488+
tm->tm_year= (tmfc.cc+1)*100-tm->tm_year+1;
3489+
}
34773490
else
3478-
tm->tm_year=tmfc.cc*100;
3491+
/* find century year for dates ending in "00" */
3492+
tm->tm_year=tmfc.cc*100+ ((tmfc.cc >=0) ?0 :1);
34793493
}
34803494
else
3495+
/* If a 4-digit year is provided, we use that and ignore CC. */
3496+
{
34813497
tm->tm_year=tmfc.year;
3498+
if (tmfc.bc&&tm->tm_year>0)
3499+
tm->tm_year=-(tm->tm_year-1);
3500+
}
34823501
}
3483-
elseif (tmfc.cc)/* use first year of century */
3484-
tm->tm_year= (tmfc.cc-1)*100+1;
3485-
3486-
if (tmfc.bc)
3502+
elseif (tmfc.cc)/* use first year of century */
34873503
{
3488-
if (tm->tm_year>0)
3489-
tm->tm_year=-(tm->tm_year-1);
3504+
if (tmfc.bc)
3505+
tmfc.cc=-tmfc.cc;
3506+
if (tmfc.cc >=0)
3507+
tm->tm_year= (tmfc.cc-1)*100+1;
34903508
else
3491-
ereport(ERROR,
3492-
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
3493-
errmsg("inconsistent use of year %04d and \"BC\"",
3494-
tm->tm_year)));
3509+
tm->tm_year=tmfc.cc*100+1;
34953510
}
34963511

34973512
if (tmfc.j)

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -992,7 +992,7 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
992992
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
993993
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
994994
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
995-
| 0,097 0097 097 97 701 1 02 07 047 16 3 1686042
995+
| 0,097 0097 097 97 7-1 1 02 07 047 16 3 1686042
996996
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
997997
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
998998
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
@@ -1063,7 +1063,7 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
10631063
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
10641064
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
10651065
| 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1066-
| 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042
1066+
| 0,097 97 97 97 7-1 1 2 7 47 16 3 1686042
10671067
| 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
10681068
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
10691069
| 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1074,7 +1074,7 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
10741074
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
10751075
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
10761076
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1077-
| 0,097 0097 097 97 701 1 02 07 047 16 3 1686042
1077+
| 0,097 0097 097 97 7-1 1 02 07 047 16 3 1686042
10781078
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
10791079
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
10801080
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
@@ -1146,7 +1146,7 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
11461146
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
11471147
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
11481148
| 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1149-
| 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042
1149+
| 0,097 97 97 97 7-1 1 2 7 47 16 3 1686042
11501150
| 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
11511151
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
11521152
| 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778

0 commit comments

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