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

Commitdb96be2

Browse files
committed
Fix handling of BC years in to_date/to_timestamp.
Previously, a conversion such asto_date('-44-02-01','YYYY-MM-DD')would result in '0045-02-01 BC', as the code attempted to interpretthe negative year as BC, but failed to apply the correction neededfor our internal handling of BC years. Fix the off-by-one problem.Also, arrange for the combination of a negative year and anexplicit "BC" marker to cancel out and produce AD. This is howthe negative-century case works, so it seems sane to do likewise.Continue to read "year 0000" as 1 BC. Oracle would throw an error,but we've accepted that case for a long time so I'm hesitant tochange it in a back-patch.Per bug #16419 from Saeed Hubaishan. Back-patch to all supportedbranches.Dar Alathar-Yemen and Tom LaneDiscussion:https://postgr.es/m/16419-d8d9db0a7553f01b@postgresql.org
1 parent071b2f7 commitdb96be2

File tree

4 files changed

+71
-2
lines changed

4 files changed

+71
-2
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6234,6 +6234,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
62346234
</para>
62356235
</listitem>
62366236

6237+
<listitem>
6238+
<para>
6239+
In <function>to_timestamp</function> and <function>to_date</function>,
6240+
negative years are treated as signifying BC. If you write both a
6241+
negative year and an explicit <literal>BC</literal> field, you get AD
6242+
again. An input of year zero is treated as 1 BC.
6243+
</para>
6244+
</listitem>
6245+
62376246
<listitem>
62386247
<para>
62396248
In <function>to_timestamp</function> and <function>to_date</function>,

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

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3733,8 +3733,11 @@ do_to_timestamp(text *date_txt, text *fmt,
37333733
{
37343734
/* If a 4-digit year is provided, we use that and ignore CC. */
37353735
tm->tm_year=tmfc.year;
3736-
if (tmfc.bc&&tm->tm_year>0)
3737-
tm->tm_year=-(tm->tm_year-1);
3736+
if (tmfc.bc)
3737+
tm->tm_year=-tm->tm_year;
3738+
/* correct for our representation of BC years */
3739+
if (tm->tm_year<0)
3740+
tm->tm_year++;
37383741
}
37393742
fmask |=DTK_M(YEAR);
37403743
}

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

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2981,6 +2981,45 @@ SELECT to_date('2458872', 'J');
29812981
01-23-2020
29822982
(1 row)
29832983

2984+
--
2985+
-- Check handling of BC dates
2986+
--
2987+
SELECT to_date('44-02-01 BC','YYYY-MM-DD BC');
2988+
to_date
2989+
---------------
2990+
02-01-0044 BC
2991+
(1 row)
2992+
2993+
SELECT to_date('-44-02-01','YYYY-MM-DD');
2994+
to_date
2995+
---------------
2996+
02-01-0044 BC
2997+
(1 row)
2998+
2999+
SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC');
3000+
to_date
3001+
------------
3002+
02-01-0044
3003+
(1 row)
3004+
3005+
SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
3006+
to_timestamp
3007+
---------------------------------
3008+
Fri Feb 01 11:12:13 0044 PST BC
3009+
(1 row)
3010+
3011+
SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS');
3012+
to_timestamp
3013+
---------------------------------
3014+
Fri Feb 01 11:12:13 0044 PST BC
3015+
(1 row)
3016+
3017+
SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
3018+
to_timestamp
3019+
------------------------------
3020+
Mon Feb 01 11:12:13 0044 PST
3021+
(1 row)
3022+
29843023
--
29853024
-- Check handling of multiple spaces in format and/or input
29863025
--
@@ -3168,6 +3207,12 @@ SELECT to_date('2016 366', 'YYYY DDD'); -- ok
31683207

31693208
SELECT to_date('2016 367', 'YYYY DDD');
31703209
ERROR: date/time field value out of range: "2016 367"
3210+
SELECT to_date('0000-02-01','YYYY-MM-DD'); -- allowed, though it shouldn't be
3211+
to_date
3212+
---------------
3213+
02-01-0001 BC
3214+
(1 row)
3215+
31713216
--
31723217
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
31733218
--

‎src/test/regress/sql/horology.sql‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -461,6 +461,17 @@ SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored
461461
SELECT to_date('3 4 21 01','W MM CC YY');
462462
SELECT to_date('2458872','J');
463463

464+
--
465+
-- Check handling of BC dates
466+
--
467+
468+
SELECT to_date('44-02-01 BC','YYYY-MM-DD BC');
469+
SELECT to_date('-44-02-01','YYYY-MM-DD');
470+
SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC');
471+
SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
472+
SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS');
473+
SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
474+
464475
--
465476
-- Check handling of multiple spaces in format and/or input
466477
--
@@ -529,6 +540,7 @@ SELECT to_date('2015 366', 'YYYY DDD');
529540
SELECT to_date('2016 365','YYYY DDD');-- ok
530541
SELECT to_date('2016 366','YYYY DDD');-- ok
531542
SELECT to_date('2016 367','YYYY DDD');
543+
SELECT to_date('0000-02-01','YYYY-MM-DD');-- allowed, though it shouldn't be
532544

533545
--
534546
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)

0 commit comments

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