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

Commit99fd38c

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 parentdb8e60b commit99fd38c

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
@@ -7639,6 +7639,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
76397639
</para>
76407640
</listitem>
76417641

7642+
<listitem>
7643+
<para>
7644+
In <function>to_timestamp</function> and <function>to_date</function>,
7645+
negative years are treated as signifying BC. If you write both a
7646+
negative year and an explicit <literal>BC</literal> field, you get AD
7647+
again. An input of year zero is treated as 1 BC.
7648+
</para>
7649+
</listitem>
7650+
76427651
<listitem>
76437652
<para>
76447653
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
@@ -4570,8 +4570,11 @@ do_to_timestamp(text *date_txt, text *fmt, Oid collid, bool std,
45704570
{
45714571
/* If a 4-digit year is provided, we use that and ignore CC. */
45724572
tm->tm_year=tmfc.year;
4573-
if (tmfc.bc&&tm->tm_year>0)
4574-
tm->tm_year=-(tm->tm_year-1);
4573+
if (tmfc.bc)
4574+
tm->tm_year=-tm->tm_year;
4575+
/* correct for our representation of BC years */
4576+
if (tm->tm_year<0)
4577+
tm->tm_year++;
45754578
}
45764579
fmask |=DTK_M(YEAR);
45774580
}

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

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2916,6 +2916,45 @@ SELECT to_date('2458872', 'J');
29162916
01-23-2020
29172917
(1 row)
29182918

2919+
--
2920+
-- Check handling of BC dates
2921+
--
2922+
SELECT to_date('44-02-01 BC','YYYY-MM-DD BC');
2923+
to_date
2924+
---------------
2925+
02-01-0044 BC
2926+
(1 row)
2927+
2928+
SELECT to_date('-44-02-01','YYYY-MM-DD');
2929+
to_date
2930+
---------------
2931+
02-01-0044 BC
2932+
(1 row)
2933+
2934+
SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC');
2935+
to_date
2936+
------------
2937+
02-01-0044
2938+
(1 row)
2939+
2940+
SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
2941+
to_timestamp
2942+
---------------------------------
2943+
Fri Feb 01 11:12:13 0044 PST BC
2944+
(1 row)
2945+
2946+
SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS');
2947+
to_timestamp
2948+
---------------------------------
2949+
Fri Feb 01 11:12:13 0044 PST BC
2950+
(1 row)
2951+
2952+
SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
2953+
to_timestamp
2954+
------------------------------
2955+
Mon Feb 01 11:12:13 0044 PST
2956+
(1 row)
2957+
29192958
--
29202959
-- Check handling of multiple spaces in format and/or input
29212960
--
@@ -3183,6 +3222,12 @@ SELECT to_date('2016 366', 'YYYY DDD'); -- ok
31833222

31843223
SELECT to_date('2016 367', 'YYYY DDD');
31853224
ERROR: date/time field value out of range: "2016 367"
3225+
SELECT to_date('0000-02-01','YYYY-MM-DD'); -- allowed, though it shouldn't be
3226+
to_date
3227+
---------------
3228+
02-01-0001 BC
3229+
(1 row)
3230+
31863231
--
31873232
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
31883233
--

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

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

429+
--
430+
-- Check handling of BC dates
431+
--
432+
433+
SELECT to_date('44-02-01 BC','YYYY-MM-DD BC');
434+
SELECT to_date('-44-02-01','YYYY-MM-DD');
435+
SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC');
436+
SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
437+
SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS');
438+
SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
439+
429440
--
430441
-- Check handling of multiple spaces in format and/or input
431442
--
@@ -511,6 +522,7 @@ SELECT to_date('2015 366', 'YYYY DDD');
511522
SELECT to_date('2016 365','YYYY DDD');-- ok
512523
SELECT to_date('2016 366','YYYY DDD');-- ok
513524
SELECT to_date('2016 367','YYYY DDD');
525+
SELECT to_date('0000-02-01','YYYY-MM-DD');-- allowed, though it shouldn't be
514526

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

0 commit comments

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