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