From 00654ad8707fef0c9bcb7a8b6a8073d1f143368a Mon Sep 17 00:00:00 2001 From: David Fetter Date: Tue, 8 Jun 2021 23:44:19 -0700 Subject: [PATCH v1] Common Era for dates --- doc/src/sgml/datatype.sgml | 25 ++++---- doc/src/sgml/datetime.sgml | 4 +- doc/src/sgml/func.sgml | 16 +++--- src/backend/utils/adt/formatting.c | 80 +++++++++++++++++++++++--- src/test/regress/expected/date.out | 72 +++++++++++------------ src/test/regress/expected/horology.out | 4 +- src/test/regress/sql/date.sql | 4 +- 7 files changed, 134 insertions(+), 71 deletions(-) diff --git doc/src/sgml/datatype.sgml doc/src/sgml/datatype.sgml index 8e30b82273..d0ca9e500f 100644 --- doc/src/sgml/datatype.sgml +++ doc/src/sgml/datatype.sgml @@ -1740,24 +1740,24 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea; timestamp [ (p) ] [ without time zone ] 8 bytes both date and time (no time zone) - 4713 BC - 294276 AD + 4713 BC[E] + 294276 AD/CE 1 microsecond timestamp [ (p) ] with time zone 8 bytes both date and time, with time zone - 4713 BC - 294276 AD + 4713 BC[E] + 294276 AD/CE 1 microsecond date 4 bytes date (no time of day) - 4713 BC - 5874897 AD + 4713 BC[E] + 5874897 AD/CE 1 day @@ -2168,23 +2168,24 @@ MINUTE TO SECOND Valid input for the time stamp types consists of the concatenation of a date and a time, followed by an optional time zone, - followed by an optional AD or BC. - (Alternatively, AD/BC can appear - before the time zone, but this is not the preferred ordering.) + followed by an optional AD, CE, + BC, or BCE + (Alternatively, AD/CE/BC/BCE + can appear before the time zone, but this is not the preferred ordering.) Thus: -1999-01-08 04:05:06 +2021-01-08 04:05:06 and: -1999-01-08 04:05:06 -8:00 +2021-01-08 04:05:06 -8:00 are valid values, which follow the ISO 8601 standard. In addition, the common format: -January 8 04:05:06 1999 PST +January 8 04:05:06 2021 PST is supported. diff --git doc/src/sgml/datetime.sgml doc/src/sgml/datetime.sgml index ecc3245a46..6266328107 100644 --- doc/src/sgml/datetime.sgml +++ doc/src/sgml/datetime.sgml @@ -167,8 +167,8 @@ - Gregorian years AD 1–99 can be entered by using 4 digits with leading - zeros (e.g., 0099 is AD 99). + Gregorian years AD/CE 1–99 can be entered by using 4 digits with leading + zeros (e.g., 0099 is AD/CE 99). diff --git doc/src/sgml/func.sgml doc/src/sgml/func.sgml index 7b652460a1..3fcb9793e7 100644 --- doc/src/sgml/func.sgml +++ doc/src/sgml/func.sgml @@ -7864,13 +7864,13 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); last digit of ISO 8601 week-numbering year - BC, bc, - AD or ad + BC, bc, BCE, bce + AD, ad, CE, ce era indicator (without periods) - B.C., b.c., - A.D. or a.d. + B.C., b.c., B.C.E., b.c.e. + A.D., a.d., C.E., or c.e. era indicator (with periods) @@ -9849,13 +9849,15 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40'); - The first century starts at 0001-01-01 00:00:00 AD, although - they did not know it at the time. This definition applies to all + The first century starts at 0001-01-01 00:00:00 AD/CE, although + the calendar did not exist at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: - Pope, Cathedral Saint-Peter of Roma, Vatican. + His Holiness, Pope Francis + Apostolic Palace + 00120 Vatican City diff --git src/backend/utils/adt/formatting.c src/backend/utils/adt/formatting.c index e909c1a200..11876dbd19 100644 --- src/backend/utils/adt/formatting.c +++ src/backend/utils/adt/formatting.c @@ -238,11 +238,19 @@ static const char *const days_short[] = { #define a_d_STR "a.d." #define AD_STR "AD" #define ad_STR "ad" +#define C_E_STR "C.E." +#define c_e_STR "c.e." +#define CE_STR "CE" +#define ce_STR "ce" #define B_C_STR "B.C." #define b_c_STR "b.c." #define BC_STR "BC" #define bc_STR "bc" +#define B_C_E_STR "B.C.E." +#define b_c_e_STR "b.c.e." +#define BCE_STR "BCE" +#define bce_STR "bce" /* * AD / BC strings for seq_search. @@ -254,8 +262,8 @@ static const char *const days_short[] = { * matches for BC have an odd index. So the boolean value for BC is given by * taking the array index of the match, modulo 2. */ -static const char *const adbc_strings[] = {ad_STR, bc_STR, AD_STR, BC_STR, NULL}; -static const char *const adbc_strings_long[] = {a_d_STR, b_c_STR, A_D_STR, B_C_STR, NULL}; +static const char *const adbc_strings[] = {ad_STR, bc_STR, AD_STR, BC_STR, CE_STR, BCE_STR, NULL}; +static const char *const adbc_strings_long[] = {a_d_STR, b_c_STR, A_D_STR, B_C_STR, c_e_STR, b_c_e_STR, C_E_STR, B_C_E_STR, NULL}; /* ---------- * AM / PM @@ -643,9 +651,13 @@ typedef enum DCH_A_M, DCH_AD, DCH_AM, + DCH_B_C_E, DCH_B_C, + DCH_BCE, DCH_BC, DCH_CC, + DCH_CE, + DCH_C_E, DCH_DAY, DCH_DDD, DCH_DD, @@ -701,9 +713,13 @@ typedef enum DCH_a_m, DCH_ad, DCH_am, + DCH_b_c_e, DCH_b_c, + DCH_bce, DCH_bc, DCH_cc, + DCH_c_e, + DCH_ce, DCH_day, DCH_ddd, DCH_dd, @@ -809,9 +825,13 @@ static const KeyWord DCH_keywords[] = { {"A.M.", 4, DCH_A_M, false, FROM_CHAR_DATE_NONE}, {"AD", 2, DCH_AD, false, FROM_CHAR_DATE_NONE}, {"AM", 2, DCH_AM, false, FROM_CHAR_DATE_NONE}, - {"B.C.", 4, DCH_B_C, false, FROM_CHAR_DATE_NONE}, /* B */ + {"B.C.E.", 6, DCH_B_C_E, false, FROM_CHAR_DATE_NONE}, /* B */ + {"B.C.", 4, DCH_B_C, false, FROM_CHAR_DATE_NONE}, + {"BCE", 3, DCH_BCE, false, FROM_CHAR_DATE_NONE}, {"BC", 2, DCH_BC, false, FROM_CHAR_DATE_NONE}, - {"CC", 2, DCH_CC, true, FROM_CHAR_DATE_NONE}, /* C */ + {"CC", 2, DCH_CC, true, FROM_CHAR_DATE_NONE}, /* C */ + {"CE", 2, DCH_CE, false, FROM_CHAR_DATE_NONE}, + {"C.E.", 4, DCH_CE, false, FROM_CHAR_DATE_NONE}, {"DAY", 3, DCH_DAY, false, FROM_CHAR_DATE_NONE}, /* D */ {"DDD", 3, DCH_DDD, true, FROM_CHAR_DATE_GREGORIAN}, {"DD", 2, DCH_DD, true, FROM_CHAR_DATE_GREGORIAN}, @@ -867,9 +887,13 @@ static const KeyWord DCH_keywords[] = { {"a.m.", 4, DCH_a_m, false, FROM_CHAR_DATE_NONE}, {"ad", 2, DCH_ad, false, FROM_CHAR_DATE_NONE}, {"am", 2, DCH_am, false, FROM_CHAR_DATE_NONE}, - {"b.c.", 4, DCH_b_c, false, FROM_CHAR_DATE_NONE}, /* b */ + {"b.c.e.", 6, DCH_b_c_e, false, FROM_CHAR_DATE_NONE}, /* b */ + {"b.c.", 4, DCH_b_c, false, FROM_CHAR_DATE_NONE}, + {"bce", 3, DCH_bce, false, FROM_CHAR_DATE_NONE}, {"bc", 2, DCH_bc, false, FROM_CHAR_DATE_NONE}, - {"cc", 2, DCH_CC, true, FROM_CHAR_DATE_NONE}, /* c */ + {"cc", 2, DCH_cc, true, FROM_CHAR_DATE_NONE}, /* c */ + {"c.e.", 4, DCH_c_e, true, FROM_CHAR_DATE_NONE}, + {"ce", 2, DCH_ce, true, FROM_CHAR_DATE_NONE}, {"day", 3, DCH_day, false, FROM_CHAR_DATE_NONE}, /* d */ {"ddd", 3, DCH_DDD, true, FROM_CHAR_DATE_GREGORIAN}, {"dd", 2, DCH_DD, true, FROM_CHAR_DATE_GREGORIAN}, @@ -985,10 +1009,10 @@ static const int DCH_index[KeyWord_INDEX_SIZE] = { -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, - -1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1, + -1, -1, -1, -1, -1, DCH_A_D, DCH_B_C_E, DCH_CC, DCH_DAY, -1, DCH_FF1, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, DCH_OF, DCH_P_M, DCH_Q, DCH_RM, DCH_SSSSS, DCH_TZH, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY, - -1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc, + -1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c_e, DCH_cc, DCH_day, -1, DCH_ff1, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi, -1, DCH_of, DCH_p_m, DCH_q, DCH_rm, DCH_sssss, DCH_tzh, DCH_us, -1, DCH_ww, -1, DCH_y_yyy, -1, -1, -1, -1 @@ -2865,6 +2889,30 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col strcpy(s, (tm->tm_year <= 0 ? bc_STR : ad_STR)); s += strlen(s); break; + case DCH_C_E: + case DCH_B_C_E: + INVALID_FOR_INTERVAL; + strcpy(s, (tm->tm_year <= 0 ? B_C_E_STR : C_E_STR)); + s += strlen(s); + break; + case DCH_CE: + case DCH_BCE: + INVALID_FOR_INTERVAL; + strcpy(s, (tm->tm_year <= 0 ? BCE_STR : CE_STR)); + s += strlen(s); + break; + case DCH_c_e: + case DCH_b_c_e: + INVALID_FOR_INTERVAL; + strcpy(s, (tm->tm_year <= 0 ? b_c_e_STR : c_e_STR)); + s += strlen(s); + break; + case DCH_ce: + case DCH_bce: + INVALID_FOR_INTERVAL; + strcpy(s, (tm->tm_year <= 0 ? bce_STR : ce_STR)); + s += strlen(s); + break; case DCH_MONTH: INVALID_FOR_INTERVAL; if (!tm->tm_mon) @@ -3570,8 +3618,12 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out, break; case DCH_A_D: case DCH_B_C: + case DCH_B_C_E: + case DCH_C_E: case DCH_a_d: case DCH_b_c: + case DCH_b_c_e: + case DCH_c_e: from_char_seq_search(&value, &s, adbc_strings_long, NULL, InvalidOid, n, have_error); @@ -3581,8 +3633,12 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out, break; case DCH_AD: case DCH_BC: + case DCH_BCE: + case DCH_CE: case DCH_ad: case DCH_bc: + case DCH_bce: + case DCH_ce: from_char_seq_search(&value, &s, adbc_strings, NULL, InvalidOid, n, have_error); @@ -3886,12 +3942,20 @@ DCH_datetime_type(FormatNode *node, bool *have_error) break; case DCH_A_D: case DCH_B_C: + case DCH_B_C_E: + case DCH_C_E: case DCH_a_d: case DCH_b_c: + case DCH_b_c_e: + case DCH_c_e: case DCH_AD: case DCH_BC: + case DCH_BCE: + case DCH_CE: case DCH_ad: case DCH_bc: + case DCH_bce: + case DCH_ce: case DCH_MONTH: case DCH_Month: case DCH_month: diff --git src/test/regress/expected/date.out src/test/regress/expected/date.out index 75ff659378..d7529f1783 100644 --- src/test/regress/expected/date.out +++ src/test/regress/expected/date.out @@ -21,10 +21,13 @@ INSERT INTO DATE_TBL VALUES ('2000-04-03'); INSERT INTO DATE_TBL VALUES ('2038-04-08'); INSERT INTO DATE_TBL VALUES ('2039-04-09'); INSERT INTO DATE_TBL VALUES ('2040-04-10'); -INSERT INTO DATE_TBL VALUES ('2040-04-10 BC'); +INSERT INTO DATE_TBL VALUES ('2040-04-10 BCE'); +ERROR: invalid input syntax for type date: "2040-04-10 BCE" +LINE 1: INSERT INTO DATE_TBL VALUES ('2040-04-10 BCE'); + ^ SELECT f1 FROM DATE_TBL; - f1 ---------------- + f1 +------------ 04-09-1957 06-13-1957 02-28-1996 @@ -40,12 +43,11 @@ SELECT f1 FROM DATE_TBL; 04-08-2038 04-09-2039 04-10-2040 - 04-10-2040 BC -(16 rows) +(15 rows) SELECT f1 FROM DATE_TBL WHERE f1 < '2000-01-01'; - f1 ---------------- + f1 +------------ 04-09-1957 06-13-1957 02-28-1996 @@ -55,8 +57,7 @@ SELECT f1 FROM DATE_TBL WHERE f1 < '2000-01-01'; 02-28-1997 03-01-1997 03-02-1997 - 04-10-2040 BC -(10 rows) +(9 rows) SELECT f1 FROM DATE_TBL WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01'; @@ -863,8 +864,7 @@ SELECT f1 - date '2000-01-01' AS "Days From 2K" FROM DATE_TBL; 13977 14343 14710 - -1475115 -(16 rows) +(15 rows) SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL; Days From Epoch @@ -884,8 +884,7 @@ SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL; 24934 25300 25667 - -1464158 -(16 rows) +(15 rows) SELECT date 'yesterday' - date 'today' AS "One day"; One day @@ -942,25 +941,24 @@ SELECT f1 as "date", date_part('julian', f1) AS julian, date_part('epoch', f1) AS epoch FROM date_tbl; - date | year | month | day | quarter | decade | century | millennium | isoyear | week | dow | isodow | doy | julian | epoch ----------------+-------+-------+-----+---------+--------+---------+------------+---------+------+-----+--------+-----+---------+--------------- - 04-09-1957 | 1957 | 4 | 9 | 2 | 195 | 20 | 2 | 1957 | 15 | 2 | 2 | 99 | 2435938 | -401760000 - 06-13-1957 | 1957 | 6 | 13 | 2 | 195 | 20 | 2 | 1957 | 24 | 4 | 4 | 164 | 2436003 | -396144000 - 02-28-1996 | 1996 | 2 | 28 | 1 | 199 | 20 | 2 | 1996 | 9 | 3 | 3 | 59 | 2450142 | 825465600 - 02-29-1996 | 1996 | 2 | 29 | 1 | 199 | 20 | 2 | 1996 | 9 | 4 | 4 | 60 | 2450143 | 825552000 - 03-01-1996 | 1996 | 3 | 1 | 1 | 199 | 20 | 2 | 1996 | 9 | 5 | 5 | 61 | 2450144 | 825638400 - 03-02-1996 | 1996 | 3 | 2 | 1 | 199 | 20 | 2 | 1996 | 9 | 6 | 6 | 62 | 2450145 | 825724800 - 02-28-1997 | 1997 | 2 | 28 | 1 | 199 | 20 | 2 | 1997 | 9 | 5 | 5 | 59 | 2450508 | 857088000 - 03-01-1997 | 1997 | 3 | 1 | 1 | 199 | 20 | 2 | 1997 | 9 | 6 | 6 | 60 | 2450509 | 857174400 - 03-02-1997 | 1997 | 3 | 2 | 1 | 199 | 20 | 2 | 1997 | 9 | 0 | 7 | 61 | 2450510 | 857260800 - 04-01-2000 | 2000 | 4 | 1 | 2 | 200 | 20 | 2 | 2000 | 13 | 6 | 6 | 92 | 2451636 | 954547200 - 04-02-2000 | 2000 | 4 | 2 | 2 | 200 | 20 | 2 | 2000 | 13 | 0 | 7 | 93 | 2451637 | 954633600 - 04-03-2000 | 2000 | 4 | 3 | 2 | 200 | 20 | 2 | 2000 | 14 | 1 | 1 | 94 | 2451638 | 954720000 - 04-08-2038 | 2038 | 4 | 8 | 2 | 203 | 21 | 3 | 2038 | 14 | 4 | 4 | 98 | 2465522 | 2154297600 - 04-09-2039 | 2039 | 4 | 9 | 2 | 203 | 21 | 3 | 2039 | 14 | 6 | 6 | 99 | 2465888 | 2185920000 - 04-10-2040 | 2040 | 4 | 10 | 2 | 204 | 21 | 3 | 2040 | 15 | 2 | 2 | 101 | 2466255 | 2217628800 - 04-10-2040 BC | -2040 | 4 | 10 | 2 | -204 | -21 | -3 | -2040 | 15 | 1 | 1 | 100 | 976430 | -126503251200 -(16 rows) + date | year | month | day | quarter | decade | century | millennium | isoyear | week | dow | isodow | doy | julian | epoch +------------+------+-------+-----+---------+--------+---------+------------+---------+------+-----+--------+-----+---------+------------ + 04-09-1957 | 1957 | 4 | 9 | 2 | 195 | 20 | 2 | 1957 | 15 | 2 | 2 | 99 | 2435938 | -401760000 + 06-13-1957 | 1957 | 6 | 13 | 2 | 195 | 20 | 2 | 1957 | 24 | 4 | 4 | 164 | 2436003 | -396144000 + 02-28-1996 | 1996 | 2 | 28 | 1 | 199 | 20 | 2 | 1996 | 9 | 3 | 3 | 59 | 2450142 | 825465600 + 02-29-1996 | 1996 | 2 | 29 | 1 | 199 | 20 | 2 | 1996 | 9 | 4 | 4 | 60 | 2450143 | 825552000 + 03-01-1996 | 1996 | 3 | 1 | 1 | 199 | 20 | 2 | 1996 | 9 | 5 | 5 | 61 | 2450144 | 825638400 + 03-02-1996 | 1996 | 3 | 2 | 1 | 199 | 20 | 2 | 1996 | 9 | 6 | 6 | 62 | 2450145 | 825724800 + 02-28-1997 | 1997 | 2 | 28 | 1 | 199 | 20 | 2 | 1997 | 9 | 5 | 5 | 59 | 2450508 | 857088000 + 03-01-1997 | 1997 | 3 | 1 | 1 | 199 | 20 | 2 | 1997 | 9 | 6 | 6 | 60 | 2450509 | 857174400 + 03-02-1997 | 1997 | 3 | 2 | 1 | 199 | 20 | 2 | 1997 | 9 | 0 | 7 | 61 | 2450510 | 857260800 + 04-01-2000 | 2000 | 4 | 1 | 2 | 200 | 20 | 2 | 2000 | 13 | 6 | 6 | 92 | 2451636 | 954547200 + 04-02-2000 | 2000 | 4 | 2 | 2 | 200 | 20 | 2 | 2000 | 13 | 0 | 7 | 93 | 2451637 | 954633600 + 04-03-2000 | 2000 | 4 | 3 | 2 | 200 | 20 | 2 | 2000 | 14 | 1 | 1 | 94 | 2451638 | 954720000 + 04-08-2038 | 2038 | 4 | 8 | 2 | 203 | 21 | 3 | 2038 | 14 | 4 | 4 | 98 | 2465522 | 2154297600 + 04-09-2039 | 2039 | 4 | 9 | 2 | 203 | 21 | 3 | 2039 | 14 | 6 | 6 | 99 | 2465888 | 2185920000 + 04-10-2040 | 2040 | 4 | 10 | 2 | 204 | 21 | 3 | 2040 | 15 | 2 | 2 | 101 | 2466255 | 2217628800 +(15 rows) -- -- epoch @@ -1043,12 +1041,10 @@ SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1 -1 (1 row) -SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 - extract ---------- - 1 -(1 row) - +SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 CE'); -- 1 +ERROR: invalid input syntax for type date: "0001-01-01 CE" +LINE 1: SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 CE'); + ^ SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 extract --------- diff --git src/test/regress/expected/horology.out src/test/regress/expected/horology.out index de73683690..75847c52d4 100644 --- src/test/regress/expected/horology.out +++ src/test/regress/expected/horology.out @@ -2181,7 +2181,7 @@ select count(*) from date_tbl where f1 not between '1997-01-01' and '1998-01-01'; count ------- - 13 + 12 (1 row) explain (costs off) @@ -2215,7 +2215,7 @@ select count(*) from date_tbl where f1 not between symmetric '1997-01-01' and '1998-01-01'; count ------- - 13 + 12 (1 row) -- diff --git src/test/regress/sql/date.sql src/test/regress/sql/date.sql index 8f7435b767..c7c3ba4e0c 100644 --- src/test/regress/sql/date.sql +++ src/test/regress/sql/date.sql @@ -20,7 +20,7 @@ INSERT INTO DATE_TBL VALUES ('2000-04-03'); INSERT INTO DATE_TBL VALUES ('2038-04-08'); INSERT INTO DATE_TBL VALUES ('2039-04-09'); INSERT INTO DATE_TBL VALUES ('2040-04-10'); -INSERT INTO DATE_TBL VALUES ('2040-04-10 BC'); +INSERT INTO DATE_TBL VALUES ('2040-04-10 BCE'); SELECT f1 FROM DATE_TBL; @@ -256,7 +256,7 @@ SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true -- millennium -- SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1 -SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 +SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 CE'); -- 1 SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2 SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2 -- 2.36.1