Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #1117: Time calculation from epoch is 12 hours out

BUG #1117: Time calculation from epoch is 12 hours out

From
"PostgreSQL Bugs List"
Date:
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

Re: BUG #1117: Time calculation from epoch is 12 hours out

From
Tom Lane
Date:
"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

Re: BUG #1117: Time calculation from epoch is 12 hours out

From
Stephan Szabo
Date:
On Fri, 26 Mar 2004, PostgreSQL Bugs List wrote:

> 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;

It seems to be a problem with reltime.  I'd suggest just avoiding reltime
entirely and using interval instead.