Re: Inconsistent behavior with TIMESTAMP WITHOUT and epoch

Josh Berkus <josh@agliodbs.com> writes:
> Summary:  "epoch" does not produce a consistent behavior when cast as
> TIMESTAMP WITHOUT TIMEZONE

I don't believe there is anything wrong here.  extract(epoch) is defined
to produce the equivalent Unix timestamp, and that's what it's doing.
See the thread at
http://archives.postgresql.org/pgsql-bugs/2003-02/msg00069.php

> test=> select extract(epoch from '2004-12-01 00:00'::TIMESTAMP WITHOUT TIME
> ZONE);
>  date_part
> ------------
>  1101888000

Seems correct assuming that you are in PST time zone.

> test=> select timestamp without time zone 'epoch' + ( interval '1 second' *
> 1101888000 );
>       ?column?
> ---------------------
>  2004-12-01 08:00:00

This is simply wrong: you should add a Unix timestamp to timestamp WITH
time zone 'epoch'.  You can cast the result to timestamp without
timezone afterward, if you feel like it.

            regards, tom lane