pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedParse CE and BCE in dates and times
4+ messages / 3 participants
[nested] [flat]
* Parse CE and BCE in dates and times
@ 2022-06-13 05:51 David Fetter <david@fetter.org>
0 siblings, 1 reply; 4+ messages in thread
From: David Fetter @ 2022-06-13 05:51 UTC (permalink / raw)
To: pgsql-hackers
Folks,
Please find attached a patch to do $Subject. As dates in a fair number
of fields of endeavor are expressed this way, it seems reasonable to
ensure tha we can parse them on input. Making it possible to use them
in output is a more invasive patch, and would involve changes to
to_date and similar that would require careful consideration.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
v1-0001-Common-Era-for-dates.patch (text/x-diff, 20.3K) download
From 00654ad8707fef0c9bcb7a8b6a8073d1f143368a Mon Sep 17 00:00:00 2001
From: David Fetter <david@fetter.org>
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;
<entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
<entry>8 bytes</entry>
<entry>both date and time (no time zone)</entry>
- <entry>4713 BC</entry>
- <entry>294276 AD</entry>
+ <entry>4713 BC[E]</entry>
+ <entry>294276 AD/CE</entry>
<entry>1 microsecond</entry>
</row>
<row>
<entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
<entry>8 bytes</entry>
<entry>both date and time, with time zone</entry>
- <entry>4713 BC</entry>
- <entry>294276 AD</entry>
+ <entry>4713 BC[E]</entry>
+ <entry>294276 AD/CE</entry>
<entry>1 microsecond</entry>
</row>
<row>
<entry><type>date</type></entry>
<entry>4 bytes</entry>
<entry>date (no time of day)</entry>
- <entry>4713 BC</entry>
- <entry>5874897 AD</entry>
+ <entry>4713 BC[E]</entry>
+ <entry>5874897 AD/CE</entry>
<entry>1 day</entry>
</row>
<row>
@@ -2168,23 +2168,24 @@ MINUTE TO SECOND
<para>
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 <literal>AD</literal> or <literal>BC</literal>.
- (Alternatively, <literal>AD</literal>/<literal>BC</literal> can appear
- before the time zone, but this is not the preferred ordering.)
+ followed by an optional <literal>AD</literal>, <literal>CE</literal>,
+ <literal>BC</literal>, or <literal>BCE</literal>
+ (Alternatively, <literal>AD</literal>/<literal>CE</literal>/<literal>BC</literal>/<literal>BCE</literal>
+ can appear before the time zone, but this is not the preferred ordering.)
Thus:
<programlisting>
-1999-01-08 04:05:06
+2021-01-08 04:05:06
</programlisting>
and:
<programlisting>
-1999-01-08 04:05:06 -8:00
+2021-01-08 04:05:06 -8:00
</programlisting>
are valid values, which follow the <acronym>ISO</acronym> 8601
standard. In addition, the common format:
<programlisting>
-January 8 04:05:06 1999 PST
+January 8 04:05:06 2021 PST
</programlisting>
is supported.
</para>
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 @@
<tip>
<para>
- Gregorian years AD 1–99 can be entered by using 4 digits with leading
- zeros (e.g., <literal>0099</literal> is AD 99).
+ Gregorian years AD/CE 1–99 can be entered by using 4 digits with leading
+ zeros (e.g., <literal>0099</literal> is AD/CE 99).
</para>
</tip>
</para>
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}');
<entry>last digit of ISO 8601 week-numbering year</entry>
</row>
<row>
- <entry><literal>BC</literal>, <literal>bc</literal>,
- <literal>AD</literal> or <literal>ad</literal></entry>
+ <entry><literal>BC</literal>, <literal>bc</literal>, <literal>BCE</literal>, <literal>bce</literal>
+ <literal>AD</literal>, <literal>ad</literal>, <literal>CE</literal>, <literal>ce</literal></entry>
<entry>era indicator (without periods)</entry>
</row>
<row>
- <entry><literal>B.C.</literal>, <literal>b.c.</literal>,
- <literal>A.D.</literal> or <literal>a.d.</literal></entry>
+ <entry><literal>B.C.</literal>, <literal>b.c.</literal>, <literal>B.C.E.</literal>, <literal>b.c.e.</literal>
+ <literal>A.D.</literal>, <literal>a.d.</literal>, <literal>C.E.</literal>, or <literal>c.e.</literal></entry>
<entry>era indicator (with periods)</entry>
</row>
<row>
@@ -9849,13 +9849,15 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
</screen>
<para>
- 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
</para>
</listitem>
</varlistentry>
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
* Re: Parse CE and BCE in dates and times
@ 2022-06-13 07:11 Erik Rijkers <er@xs4all.nl>
parent: David Fetter <david@fetter.org>
0 siblings, 1 reply; 4+ messages in thread
From: Erik Rijkers @ 2022-06-13 07:11 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; pgsql-hackers
Op 13-06-2022 om 07:51 schreef David Fetter:
> Folks,
>
> Please find attached a patch to do $Subject. As dates in a fair number
> of fields of endeavor are expressed this way, it seems reasonable to
> ensure tha we can parse them on input. Making it possible to use them
> in output is a more invasive patch, and would involve changes to
> to_date and similar that would require careful consideration.
Hi David,
I find some unexpected results:
# select '112-04-30 BC'::date;
date
---------------
0112-04-30 BC
(1 row)
but the same with the ' BCE' suffix seems broken:
# select '112-04-30 BCE'::date;
ERROR: invalid input syntax for type date: "112-04-30 BCE"
LINE 1: select '112-04-30 BCE'::date;
The same goes for '112-04-30 AD' (works) and its CE version (errors out).
Or is this as expected?
Erik Rijkers
>
> Best,
> David.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Parse CE and BCE in dates and times
@ 2022-06-13 14:39 David Fetter <david@fetter.org>
parent: Erik Rijkers <er@xs4all.nl>
0 siblings, 1 reply; 4+ messages in thread
From: David Fetter @ 2022-06-13 14:39 UTC (permalink / raw)
To: Erik Rijkers <er@xs4all.nl>; +Cc: pgsql-hackers
On Mon, Jun 13, 2022 at 09:11:56AM +0200, Erik Rijkers wrote:
> Op 13-06-2022 om 07:51 schreef David Fetter:
> > Folks,
> >
> > Please find attached a patch to do $Subject. As dates in a fair number
> > of fields of endeavor are expressed this way, it seems reasonable to
> > ensure tha we can parse them on input. Making it possible to use them
> > in output is a more invasive patch, and would involve changes to
> > to_date and similar that would require careful consideration.
>
> Hi David,
>
> I find some unexpected results:
>
> # select '112-04-30 BC'::date;
> date
> ---------------
> 0112-04-30 BC
> (1 row)
>
> but the same with the ' BCE' suffix seems broken:
>
> # select '112-04-30 BCE'::date;
> ERROR: invalid input syntax for type date: "112-04-30 BCE"
> LINE 1: select '112-04-30 BCE'::date;
>
> The same goes for '112-04-30 AD' (works) and its CE version (errors out).
>
> Or is this as expected?
It's not, and thanks for looking at this. Will check to see what's
going on here.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Parse CE and BCE in dates and times
@ 2022-08-02 18:13 Jacob Champion <jchampion@timescale.com>
parent: David Fetter <david@fetter.org>
0 siblings, 0 replies; 4+ messages in thread
From: Jacob Champion @ 2022-08-02 18:13 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; Erik Rijkers <er@xs4all.nl>; +Cc: pgsql-hackers
This entry has been waiting on author input for a while (our current
threshold is roughly two weeks), so I've marked it Returned with
Feedback.
Once you think the patchset is ready for review again, you (or any
interested party) can resurrect the patch entry by visiting
https://commitfest.postgresql.org/38/3682/
and changing the status to "Needs Review", and then changing the
status again to "Move to next CF". (Don't forget the second step;
hopefully we will have streamlined this in the near future!)
Thanks,
--Jacob
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-08-02 18:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-13 05:51 Parse CE and BCE in dates and times David Fetter <david@fetter.org>
2022-06-13 07:11 ` Erik Rijkers <er@xs4all.nl>
2022-06-13 14:39 ` David Fetter <david@fetter.org>
2022-08-02 18:13 ` Jacob Champion <jchampion@timescale.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox