Earliest Timestamp supported in PostgreSQL

Question

I work with different databases in a number of different time zones (and periods of time) and one thing that normally originates problems, is the date/time definition.

For this reason, and since a date is a reference to a starting value, to keep track of how it was calculated, I try to store the base date; i.e.: the minimum date supported in that particular computer/database;

If I am seeing it well, this depends on the RDBMS and on the particular storage of the type. In SQL Server, I found a couple of ways of calculating this "base date";

SELECT CONVERT(DATETIME, 0) 

or

SELECT DATEADD(MONTH, 0, 0 ) 

or even a cast like this:

DECLARE @300 BINARY(8) 
SET @300 = 0x00000000 + CAST(300 AS BINARY(4))
set @dt=(SELECT CAST(@300 AS DATETIME) AS BASEDATE)
print CAST(@dt AS NVARCHAR(100))

(where @dt is a datetime variable)

My question is, is there a similar way of calculating the base date in PostgreSQL, i.e.: the value that is the minimum date supported and is on the base of all calculations?

From the description of the date type, I can see that the minimum date supported is 4713 BC, but is there a way of getting this value programmatically (for instance as a formatted date string), as I do in SQL Server?

Solution 1

The manual states the values as:

• Low value: 4713 BC
• High value: 294276 AD

with the caveat, as Chris noted, that -infinity is also supported.

See the note later in the same page in the manual; the above is only true if you are using integer timestamps, which are the default in all vaguely recent versions of PostgreSQL. If in doubt:

SHOW integer_datetimes;

will tell you. If you're using floating point datetimes instead, you get greater range and less (non-linear) precision. Any attempt to work out the minimum programatically must cope with that restriction.

PostgreSQL does not just let you cast zero to a timestamp to get the minimum possible timestamp, nor would this make much sense if you were using floating point datetimes. You can use the julian date conversion function, but this gives you the epoch not the minimum time:

postgres=> select to_timestamp(0);
      to_timestamp      
------------------------
 1970-01-01 08:00:00+08
(1 row)

because it accepts negative values. You'd think that giving it negative maxint would work, but the results are surprising to the point where I wonder if we've got a wrap-around bug lurking here:

postgres=> select to_timestamp(-922337203685477);
          to_timestamp           
---------------------------------
 294247-01-10 12:00:54.775808+08
(1 row)

postgres=> select to_timestamp(-92233720368547);
          to_timestamp           
---------------------------------
 294247-01-10 12:00:54.775808+08
(1 row)

postgres=> select to_timestamp(-9223372036854);
         to_timestamp         
------------------------------
 294247-01-10 12:00:55.552+08
(1 row)

postgres=> select to_timestamp(-922337203685);
ERROR:  timestamp out of range
postgres=> select to_timestamp(-92233720368);
          to_timestamp           
---------------------------------
 0954-03-26 09:50:36+07:43:24 BC
(1 row)

postgres=> select to_timestamp(-9223372036);
         to_timestamp         
------------------------------
 1677-09-21 07:56:08+07:43:24
(1 row)

(Perhaps related to the fact that to_timestamp takes a double, even though timestamps are stored as integers these days?).

I think it's possibly wisest to just let the timestamp range be any timestamp you don't get an error on. After all, the range of valid timestamps is not continuous:

postgres=> SELECT TIMESTAMP '2000-02-29';
      timestamp      
---------------------
 2000-02-29 00:00:00
(1 row)

postgres=> SELECT TIMESTAMP '2001-02-29';
ERROR:  date/time field value out of range: "2001-02-29"
LINE 1: SELECT TIMESTAMP '2001-02-29';

so you can't assume that just because a value is between two valid timestamps, it is its self valid.

Solution 2

The earliest timestamp is '-infinity'. This is a special value. The other side is 'infinity' which is later than any specific timestamp.

I don't know of a way of getting this programaticly. I would just use the value hard-coded the way you might use NULL. That means you have to handle infinities on the client side though.