Skip to content

Commit b0fe0b0

Browse files
tglsfdctglsfdc
committed
Fix handling of BC years in to_date/to_timestamp.
Previously, a conversion such as to_date('-44-02-01','YYYY-MM-DD') would result in '0045-02-01 BC', as the code attempted to interpret the negative year as BC, but failed to apply the correction needed for our internal handling of BC years. Fix the off-by-one problem. Also, arrange for the combination of a negative year and an explicit "BC" marker to cancel out and produce AD. This is how the 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 to change it in a back-patch. Per bug #16419 from Saeed Hubaishan. Back-patch to all supported branches. Dar Alathar-Yemen and Tom Lane Discussion: https://postgr.es/m/16419-d8d9db0a7553f01b@postgresql.org
1 parent ab0c9c0 commit b0fe0b0

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

6345+
<listitem>
6346+
<para>
6347+
In <function>to_timestamp</function> and <function>to_date</function>,
6348+
negative years are treated as signifying BC. If you write both a
6349+
negative year and an explicit <literal>BC</literal> field, you get AD
6350+
again. An input of year zero is treated as 1 BC.
6351+
</para>
6352+
</listitem>
6353+
63456354
<listitem>
63466355
<para>
63476356
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
@@ -3754,8 +3754,11 @@ do_to_timestamp(text *date_txt, text *fmt,
37543754
{
37553755
/* If a 4-digit year is provided, we use that and ignore CC. */
37563756
tm->tm_year = tmfc.year;
3757-
if (tmfc.bc && tm->tm_year > 0)
3758-
tm->tm_year = -(tm->tm_year - 1);
3757+
if (tmfc.bc)
3758+
tm->tm_year = -tm->tm_year;
3759+
/* correct for our representation of BC years */
3760+
if (tm->tm_year < 0)
3761+
tm->tm_year++;
37593762
}
37603763
fmask |= DTK_M(YEAR);
37613764
}

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

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3011,6 +3011,45 @@ SELECT to_date('2458872', 'J');
30113011
01-23-2020
30123012
(1 row)
30133013

3014+
--
3015+
-- Check handling of BC dates
3016+
--
3017+
SELECT to_date('44-02-01 BC','YYYY-MM-DD BC');
3018+
to_date
3019+
---------------
3020+
02-01-0044 BC
3021+
(1 row)
3022+
3023+
SELECT to_date('-44-02-01','YYYY-MM-DD');
3024+
to_date
3025+
---------------
3026+
02-01-0044 BC
3027+
(1 row)
3028+
3029+
SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC');
3030+
to_date
3031+
------------
3032+
02-01-0044
3033+
(1 row)
3034+
3035+
SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
3036+
to_timestamp
3037+
---------------------------------
3038+
Fri Feb 01 11:12:13 0044 PST BC
3039+
(1 row)
3040+
3041+
SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS');
3042+
to_timestamp
3043+
---------------------------------
3044+
Fri Feb 01 11:12:13 0044 PST BC
3045+
(1 row)
3046+
3047+
SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
3048+
to_timestamp
3049+
------------------------------
3050+
Mon Feb 01 11:12:13 0044 PST
3051+
(1 row)
3052+
30143053
--
30153054
-- Check handling of multiple spaces in format and/or input
30163055
--
@@ -3198,6 +3237,12 @@ SELECT to_date('2016 366', 'YYYY DDD'); -- ok
31983237

31993238
SELECT to_date('2016 367', 'YYYY DDD');
32003239
ERROR: date/time field value out of range: "2016 367"
3240+
SELECT to_date('0000-02-01','YYYY-MM-DD'); -- allowed, though it shouldn't be
3241+
to_date
3242+
---------------
3243+
02-01-0001 BC
3244+
(1 row)
3245+
32013246
--
32023247
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
32033248
--

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

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

470+
--
471+
-- Check handling of BC dates
472+
--
473+
474+
SELECT to_date('44-02-01 BC','YYYY-MM-DD BC');
475+
SELECT to_date('-44-02-01','YYYY-MM-DD');
476+
SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC');
477+
SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
478+
SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS');
479+
SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC');
480+
470481
--
471482
-- Check handling of multiple spaces in format and/or input
472483
--
@@ -535,6 +546,7 @@ SELECT to_date('2015 366', 'YYYY DDD');
535546
SELECT to_date('2016 365', 'YYYY DDD'); -- ok
536547
SELECT to_date('2016 366', 'YYYY DDD'); -- ok
537548
SELECT to_date('2016 367', 'YYYY DDD');
549+
SELECT to_date('0000-02-01','YYYY-MM-DD'); -- allowed, though it shouldn't be
538550

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

0 commit comments

Comments
 (0)