Thread: PG producing odd results on epoch timestamp to string conversion

Hi folks,

I've got a web app which runs on three databases: PostgreSQL, Oracle
and Microsoft SQL Server.

This app has columns which contain Unix epoch timestamps generated
from Python's time.time() function. Now I need to write DB functions
or expressions which convert these timestamps into human-readable
format, ideally in the local timezone.  These are the expressions I've
come up with so far, using 1315503340 as a test value:

PostgreSQL (testing with 8.4.3 on OS X):

SELECT TO_CHAR( TIMESTAMP WITH TIME ZONE 'epoch' + 1315503340 *
INTERVAL '1 second', 'MM-DD-YYYY HH:MM:SS TZ');
09-08-2011 12:09:40 CDT

SQL Server 2008:

select cast(DATEADD(second, 1315503340, '1970-01-01 00:00:00') as
datetimeoffset)
2011-09-08 17:35:40.0000000 +00:00

Oracle 11:

select to_char( cast( to_timestamp_tz('01-jan-1970 00:00:00 -00:00',
'dd-mon-yyyy HH24:MI:SS TZH:TZM') as timestamp with local time zone) +
numtodsinterval ( TIMESTAMP , 'second'), 'mm-dd-yyyy HH24:MI:SS TZR')
from dual
09-08-2011 12:35:40 -05:00

For comparison, here's Python (2.6.5 on OS X):

>>> print time.asctime(time.localtime(1315503340))
Thu Sep  8 12:35:40 2011

As you can see, Python, SQL Server, and Oracle all agree that the
timestamp 1315503340 means 12:35:40 CDT on that date. Yet PostgreSQL
shows a value that is exactly 26 minutes behind the others (12:09:40).

Can anyone help me understand why this discrepancy, and/or how to deal
with it?  Is it something like PG actually accounting for civil time
oddities, slightly variations in the earth's orbit, or something like
that?  I know a lot of algorithms assume exactly 86,400 seconds in a
day, which isn't always true. You'd think if it was a simple timezone
issue it would be off by increments of 1 hour. For the purposes of my
application at least, PostgreSQL is producing a "wrong" value here (it
needs to match up with Python) and I'd like to figure out how to get
the "right" value.  I guess just adding 26 minutes to the PostgreSQL
value is one way but that seems kind of dangerous without
understanding what's really going on here.

If any light is shed on this I would be greatly appreciative.

Regards,

Preston Landers

Preston Landers <planders@gmail.com> writes:
> SELECT TO_CHAR( TIMESTAMP WITH TIME ZONE 'epoch' + 1315503340 *
> INTERVAL '1 second', 'MM-DD-YYYY HH:MM:SS TZ');
> 09-08-2011 12:09:40 CDT

> As you can see, Python, SQL Server, and Oracle all agree that the
> timestamp 1315503340 means 12:35:40 CDT on that date.

So does Postgres.

regression=# set timezone = 'CST6CDT';
SET
regression=# select TIMESTAMP WITH TIME ZONE 'epoch' + 1315503340 *
INTERVAL '1 second';
        ?column?
------------------------
 2011-09-08 12:35:40-05
(1 row)

> Yet PostgreSQL
> shows a value that is exactly 26 minutes behind the others (12:09:40).

You've fat-fingered the to_char usage --- MM is month, not minutes
(I think you want MI for that).

            regards, tom lane

On Fri, Sep 9, 2011 at 5:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yet PostgreSQL
>> shows a value that is exactly 26 minutes behind the others (12:09:40).
>
> You've fat-fingered the to_char usage --- MM is month, not minutes
> (I think you want MI for that).
>

Yep, that's exactly it.  Thanks so much for your help, and everything
else you've done for this project over the years!

best regards,
Preston