pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vik Fearing <vik@postgresfriends.org>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: jian he <jian.universality@gmail.com>
Subject: +infinity for dates and timestamps
Date: Sun, 1 Jan 2023 03:10:23 +0100
Message-ID: <d0bef637-2dbd-0a5d-e539-48243b6f6c5e@postgresfriends.org> (raw)

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');

view thread (5+ messages)  latest in thread

Message-ID: <d0bef637-2dbd-0a5d-e539-48243b6f6c5e@postgresfriends.org>
Permalink:  ../d0bef637-2dbd-0a5d-e539-48243b6f6c5e@postgresfriends.org/
Also on:    postgresql.org/message-id/d0bef637-2dbd-0a5d-e539-48243b6f6c5e@postgresfriends.org

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-hackers@postgresql.org
  Cc: vik@postgresfriends.org, jian.universality@gmail.com
  Subject: Re: +infinity for dates and timestamps
  In-Reply-To: <d0bef637-2dbd-0a5d-e539-48243b6f6c5e@postgresfriends.org>
  References: <d0bef637-2dbd-0a5d-e539-48243b6f6c5e@postgresfriends.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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