Re: BUG #1117: Time calculation from epoch is 12 hours out - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #1117: Time calculation from epoch is 12 hours out
Date
Msg-id 1400.1080358971@sss.pgh.pa.us
Whole thread Raw
In response to BUG #1117: Time calculation from epoch is 12 hours out  ("PostgreSQL Bugs List" <pgsql-bugs@postgresql.org>)
List pgsql-bugs
"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> The output of the following query:
> select '1970-1-1 00:00:00'::timestamp + '1080302400 seconds'::reltime;

> gives: 2004-03-26 00:00:00. This is apparently exactly 12 hours wrong. I
> believe the query should have given me: 2004-03-26 12:00:00.

This does seem broken, but I'm not sure anyone is going to bother to fix
it.  Type reltime is deprecated and hasn't even been documented for
years --- if I were going to spend any effort on it, it'd be to rip it
out ;-)

Use "interval", which is SQL-standard and does give the right answer.

regression=# select '1970-1-1 00:00:00'::timestamp + '1080302400 seconds'::reltime;
      ?column?
---------------------
 2004-03-26 00:00:00
(1 row)

regression=# select '1970-1-1 00:00:00'::timestamp + '1080302400 seconds'::interval;
      ?column?
---------------------
 2004-03-26 12:00:00
(1 row)

AFAICS from quick poking at it, the bug is in the reltime input code,
not in the subsequent addition.

            regards, tom lane

pgsql-bugs by date:

Previous
From: "Matthew T. O'Connor"
Date:
Subject: Re: Fwd: Default pg_autovacuum config glitches
Next
From: Stephan Szabo
Date:
Subject: Re: BUG #1117: Time calculation from epoch is 12 hours out