BUG #1117: Time calculation from epoch is 12 hours out
The following bug has been logged online:
Bug reference: 1117
Logged by: Neil Cooper
Email address: neil.cooper@scigames.com
PostgreSQL version: 7.3.4
Operating system: Red Hat ES 3.0
Description: Time calculation from epoch is 12 hours out
Details:
I'm using the PostgreSQL that comes with Red Hat ES 3.0. (psql reports
7.3.4-R.H). For purposes of testing/reporting, my workstation is running on
UTC time to avoid any potential timezone confusions.
OK heres the problem:
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.
I got the value 1080302400 by writing a simple C program that repeatedly
calls gettimeofday() and prints the result, so I'm pretty sure the value
itself is correct.
I also checked that its not a problem with am/pm wrapping by running the
same query with 1 second less as the time. It incorrectly(?) gives:
2004-03-25 23:59:59
instead of:
2004-03-26 11:59:59
Please can someone confirm whether this is a bug or whether I'm doing
something wrong, as I'm desperately trying to deliver an already overdue
project and this is causing additional delays!
Thanks in anticipation!
Neil Cooper
"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
On Fri, 26 Mar 2004, PostgreSQL Bugs List wrote:
The following bug has been logged online:
Bug reference: 1117
Logged by: Neil CooperEmail address: neil.cooper@scigames.com
PostgreSQL version: 7.3.4
Operating system: Red Hat ES 3.0
Description: Time calculation from epoch is 12 hours out
Details:
I'm using the PostgreSQL that comes with Red Hat ES 3.0. (psql reports
7.3.4-R.H). For purposes of testing/reporting, my workstation is running on
UTC time to avoid any potential timezone confusions.OK heres the problem:
The output of the following query:
select '1970-1-1 00:00:00'::timestamp + '1080302400 seconds'::reltime;
It seems to be a problem with reltime. I'd suggest just avoiding reltime
entirely and using interval instead.