pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed+infinity for dates and timestamps
5+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2023-01-01 2ceea5adb0 Accept "+infinity" in date and timestamp[tz] input.
* +infinity for dates and timestamps
@ 2023-01-01 02:10 Vik Fearing <vik@postgresfriends.org>
2023-01-01 02:31 ` Re: +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
2023-01-01 05:24 ` Re: +infinity for dates and timestamps Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Vik Fearing @ 2023-01-01 02:10 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; +Cc: jian he <jian.universality@gmail.com>
It has always annoyed me that we can't write '+infinity' for dates and
timestamps and get the OCD satisfaction of making our queries line up
with '-infinity'.
I wrote a fix for that some time ago but apparently never posted it. I
was reminded of it by jian he in the Infinite Interval thread, and so
here it is.
--
Vik Fearing
Attachments:
plus_infinity.diff (text/x-patch, 3.6K) download
commit 5178a17a3280bc0018194e590d1b9fb3afbe3b65
Author: Vik Fearing <vik@postgresfriends.org>
Date: Tue Jun 7 00:22:21 2022 +0200
allow +infinity for dates
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index fdffba4442..863ab84442 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2288,7 +2288,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
<productname>PostgreSQL</productname> supports several
special date/time input values for convenience, as shown in <xref
linkend="datatype-datetime-special-table"/>. The values
- <literal>infinity</literal> and <literal>-infinity</literal>
+ <literal>infinity</literal> (alternatively <literal>+infinity</literal>) and <literal>-infinity</literal>
are specially represented inside the system and will be displayed
unchanged; but the others are simply notational shorthands
that will be converted to ordinary date/time values when read.
@@ -2315,7 +2315,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
<entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
</row>
<row>
- <entry><literal>infinity</literal></entry>
+ <entry><literal>infinity</literal> or <literal>+infinity</literal></entry>
<entry><type>date</type>, <type>timestamp</type></entry>
<entry>later than all other time stamps</entry>
</row>
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 8afda0e5d2..6b2da1acfe 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -105,6 +105,7 @@ const char *const days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
*/
static const datetkn datetktbl[] = {
/* token, type, value */
+ {LATE2, RESERV, DTK_LATE}, /* "+infinity" reserved for "late time" */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
{DA_D, ADBC, AD}, /* "ad" for years > 0 */
{"allballs", RESERV, DTK_ZULU}, /* 00:00:00 */
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index bdb7c06bec..d503d8b24d 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -38,6 +38,7 @@ struct tzEntry;
#define INVALID "invalid"
#define EARLY "-infinity"
#define LATE "infinity"
+#define LATE2 "+infinity"
#define NOW "now"
#define TODAY "today"
#define TOMORROW "tomorrow"
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index 9fd15be5f9..1dcb07b78f 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -329,6 +329,7 @@ select 'infinity'::date, '-infinity'::date;
select 'infinity'::date > 'today'::date as t;
select '-infinity'::date < 'today'::date as t;
select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date);
+select 'infinity'::date = '+infinity'::date as t;
--
-- oscillating fields from non-finite date:
--
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index e1175b12ce..004d1d3454 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -49,6 +49,10 @@ INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
+-- Make sure 'infinity' and '+infinity' are the same
+SELECT timestamp 'infinity' = timestamp '+infinity' AS t;
+SELECT timestamptz 'infinity' = timestamptz '+infinity' AS t;
+
-- Postgres v6.0 standard output format
INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: +infinity for dates and timestamps
2023-01-01 02:10 +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
@ 2023-01-01 02:31 ` Vik Fearing <vik@postgresfriends.org>
2023-01-01 19:21 ` Re: +infinity for dates and timestamps Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Vik Fearing @ 2023-01-01 02:31 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; +Cc: jian he <jian.universality@gmail.com>
On 1/1/23 03:10, Vik Fearing wrote:
> It has always annoyed me that we can't write '+infinity' for dates and
> timestamps and get the OCD satisfaction of making our queries line up
> with '-infinity'.
>
> I wrote a fix for that some time ago but apparently never posted it. I
> was reminded of it by jian he in the Infinite Interval thread, and so
> here it is.
Hmm. Somehow the .out test files were not included.
Fixed with attached.
--
Vik Fearing
Attachments:
plus_infinity.v2.diff (text/x-patch, 8.4K) download
commit 5178a17a3280bc0018194e590d1b9fb3afbe3b65
Author: Vik Fearing <vik@postgresfriends.org>
Date: Tue Jun 7 00:22:21 2022 +0200
allow +infinity for dates
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index fdffba4442..863ab84442 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2288,7 +2288,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
<productname>PostgreSQL</productname> supports several
special date/time input values for convenience, as shown in <xref
linkend="datatype-datetime-special-table"/>. The values
- <literal>infinity</literal> and <literal>-infinity</literal>
+ <literal>infinity</literal> (alternatively <literal>+infinity</literal>) and <literal>-infinity</literal>
are specially represented inside the system and will be displayed
unchanged; but the others are simply notational shorthands
that will be converted to ordinary date/time values when read.
@@ -2315,7 +2315,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
<entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
</row>
<row>
- <entry><literal>infinity</literal></entry>
+ <entry><literal>infinity</literal> or <literal>+infinity</literal></entry>
<entry><type>date</type>, <type>timestamp</type></entry>
<entry>later than all other time stamps</entry>
</row>
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 8afda0e5d2..6b2da1acfe 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -105,6 +105,7 @@ const char *const days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
*/
static const datetkn datetktbl[] = {
/* token, type, value */
+ {LATE2, RESERV, DTK_LATE}, /* "+infinity" reserved for "late time" */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
{DA_D, ADBC, AD}, /* "ad" for years > 0 */
{"allballs", RESERV, DTK_ZULU}, /* 00:00:00 */
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index bdb7c06bec..d503d8b24d 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -38,6 +38,7 @@ struct tzEntry;
#define INVALID "invalid"
#define EARLY "-infinity"
#define LATE "infinity"
+#define LATE2 "+infinity"
#define NOW "now"
#define TODAY "today"
#define TOMORROW "tomorrow"
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index 9fd15be5f9..1dcb07b78f 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -329,6 +329,7 @@ select 'infinity'::date, '-infinity'::date;
select 'infinity'::date > 'today'::date as t;
select '-infinity'::date < 'today'::date as t;
select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date);
+select 'infinity'::date = '+infinity'::date as t;
--
-- oscillating fields from non-finite date:
--
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index e1175b12ce..004d1d3454 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -49,6 +49,10 @@ INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
+-- Make sure 'infinity' and '+infinity' are the same
+SELECT timestamp 'infinity' = timestamp '+infinity' AS t;
+SELECT timestamptz 'infinity' = timestamptz '+infinity' AS t;
+
-- Postgres v6.0 standard output format
INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
commit 3ae288cc9684a122b08e0e61174bb63b28309110
Author: Vik Fearing <vik@postgresfriends.org>
Date: Tue Jun 7 00:22:21 2022 +0200
allow +infinity for dates
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index fdffba4442..863ab84442 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2288,7 +2288,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
<productname>PostgreSQL</productname> supports several
special date/time input values for convenience, as shown in <xref
linkend="datatype-datetime-special-table"/>. The values
- <literal>infinity</literal> and <literal>-infinity</literal>
+ <literal>infinity</literal> (alternatively <literal>+infinity</literal>) and <literal>-infinity</literal>
are specially represented inside the system and will be displayed
unchanged; but the others are simply notational shorthands
that will be converted to ordinary date/time values when read.
@@ -2315,7 +2315,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
<entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
</row>
<row>
- <entry><literal>infinity</literal></entry>
+ <entry><literal>infinity</literal> or <literal>+infinity</literal></entry>
<entry><type>date</type>, <type>timestamp</type></entry>
<entry>later than all other time stamps</entry>
</row>
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 8afda0e5d2..6b2da1acfe 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -105,6 +105,7 @@ const char *const days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
*/
static const datetkn datetktbl[] = {
/* token, type, value */
+ {LATE2, RESERV, DTK_LATE}, /* "+infinity" reserved for "late time" */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
{DA_D, ADBC, AD}, /* "ad" for years > 0 */
{"allballs", RESERV, DTK_ZULU}, /* 00:00:00 */
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index bdb7c06bec..d503d8b24d 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -38,6 +38,7 @@ struct tzEntry;
#define INVALID "invalid"
#define EARLY "-infinity"
#define LATE "infinity"
+#define LATE2 "+infinity"
#define NOW "now"
#define TODAY "today"
#define TOMORROW "tomorrow"
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index f8f83e40e9..c0dec448e1 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -1373,6 +1373,12 @@ select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
f | f | t
(1 row)
+select 'infinity'::date = '+infinity'::date as t;
+ t
+---
+ t
+(1 row)
+
--
-- oscillating fields from non-finite date:
--
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index be66274738..c494e84cd9 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -76,6 +76,19 @@ TRUNCATE TIMESTAMP_TBL;
INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
+-- Make sure 'infinity' and '+infinity' are the same
+SELECT timestamp 'infinity' = timestamp '+infinity' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT timestamptz 'infinity' = timestamptz '+infinity' AS t;
+ t
+---
+ t
+(1 row)
+
-- Postgres v6.0 standard output format
INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
-- Variations on Postgres v6.1 standard output format
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index 9fd15be5f9..1dcb07b78f 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -329,6 +329,7 @@ select 'infinity'::date, '-infinity'::date;
select 'infinity'::date > 'today'::date as t;
select '-infinity'::date < 'today'::date as t;
select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date);
+select 'infinity'::date = '+infinity'::date as t;
--
-- oscillating fields from non-finite date:
--
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index e1175b12ce..004d1d3454 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -49,6 +49,10 @@ INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
+-- Make sure 'infinity' and '+infinity' are the same
+SELECT timestamp 'infinity' = timestamp '+infinity' AS t;
+SELECT timestamptz 'infinity' = timestamptz '+infinity' AS t;
+
-- Postgres v6.0 standard output format
INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: +infinity for dates and timestamps
2023-01-01 02:10 +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
2023-01-01 02:31 ` Re: +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
@ 2023-01-01 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-01 19:22 ` Re: +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-01-01 19:21 UTC (permalink / raw)
To: Vik Fearing <vik@postgresfriends.org>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; jian he <jian.universality@gmail.com>
Vik Fearing <vik@postgresfriends.org> writes:
> Hmm. Somehow the .out test files were not included.
> Fixed with attached.
Somehow you'd managed to duplicate some of the other changes,
so the cfbot still didn't like that :-(
Anyway, pushed with cosmetic changes. Notably, I left out the
documentation changes after observing that we don't document
"+infinity" separately for the numeric types. Given the lack of
complaints about that I think it's fine to do the same here.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: +infinity for dates and timestamps
2023-01-01 02:10 +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
2023-01-01 02:31 ` Re: +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
2023-01-01 19:21 ` Re: +infinity for dates and timestamps Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-01 19:22 ` Vik Fearing <vik@postgresfriends.org>
0 siblings, 0 replies; 5+ messages in thread
From: Vik Fearing @ 2023-01-01 19:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; jian he <jian.universality@gmail.com>
On 1/1/23 20:21, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
>> Hmm. Somehow the .out test files were not included.
>> Fixed with attached.
>
> Somehow you'd managed to duplicate some of the other changes,
> so the cfbot still didn't like that :-(
>
> Anyway, pushed with cosmetic changes. Notably, I left out the
> documentation changes after observing that we don't document
> "+infinity" separately for the numeric types. Given the lack of
> complaints about that I think it's fine to do the same here.
Thanks, Tom! No objections to your changes.
--
Vik Fearing
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: +infinity for dates and timestamps
2023-01-01 02:10 +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
@ 2023-01-01 05:24 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2023-01-01 05:24 UTC (permalink / raw)
To: Vik Fearing <vik@postgresfriends.org>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; jian he <jian.universality@gmail.com>
Vik Fearing <vik@postgresfriends.org> writes:
> It has always annoyed me that we can't write '+infinity' for dates and
> timestamps and get the OCD satisfaction of making our queries line up
> with '-infinity'.
+1, since it works for numerics it should work for these types too.
(I didn't read the patch though.)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-01-01 19:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-01 02:10 +infinity for dates and timestamps Vik Fearing <vik@postgresfriends.org>
2023-01-01 02:31 ` Vik Fearing <vik@postgresfriends.org>
2023-01-01 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-01 19:22 ` Vik Fearing <vik@postgresfriends.org>
2023-01-01 05:24 ` Tom Lane <tgl@sss.pgh.pa.us>
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