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