CODEWITHSUNDEEP
sqlpostgresql
Irenej Marc
Irenej Marc

Reputation: 302

Very large intervals in PostgreSQL

I'm trying to use a piece of code like this.

SELECT now () + interval '50000000000s'

Which is a problem because the number of seconds is too large for the interval. According to the PostgreSQL online docs, interval is a 16-byte number, but that only seems to be the case when actually used as a column in a table.

I've tried summing two timestamps but an operator + simply does not exist on two timestamps (- does, though).

Is there any other way to add a very large interval (or number of seconds) to an existing timestamp, either stored in a table or now().

PS: Converting to UNIX timestamp, adding the number of seconds and converting back to the PostgreSQL's timestamp would probably work but I am looking for a more direct solution.

Upvotes: 2

Views: 397

Answers (2)

Vucar Timnärakrul
Vucar Timnärakrul

Reputation: 552

An interval is indeed 16 bytes long, but the internal representation is difficult (well, everything that has to do with dates and times is…). To quote the manual:

Internally interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the seconds field can store fractions. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases. Functions justify_days and justify_hours are available for adjusting days and hours that overflow their normal ranges.

So in your case, it’s the ‘seconds’ field that overflows, even though the interval itself is perfectly able to handle 50 billion seconds, as Clodoaldo correctly pointed out:

SELECT INTERVAL '1 second' * 50000000000;

Note that intervals are floating point values, so precision will deteriorate the further you move away from the year 2000.

As a side note: There’s no + operator for timestamps since they make about as much sense as for temperatures: absolutely none. What is ‘March 20, 13:40 + April 4, 15:33’ supposed to mean? The difference between two timestamps on the other hand is well-defined: an interval.

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

select now() + 50000000000 * interval '1 second';
           ?column?           
------------------------------
 3599-03-23 12:07:44.43808+00

Upvotes: 5

Related Questions