From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | josh(at)agliodbs(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Inconsistent behavior with TIMESTAMP WITHOUT and epoch |
Date: | 2005-01-26 19:04:49 |
Message-ID: | 17708.1106766289@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Josh Berkus <josh(at)agliodbs(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Koei Kim | 2005-01-27 04:40:22 | BUG #1441: casting problem when lower limit, ERROR out of range |
Previous Message | Dennis Hübner | 2005-01-26 16:07:16 | No connection to Server |