Re: Inconsistent behavior with TIMESTAMP WITHOUT and epoch

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

In response to

Responses

Browse pgsql-bugs by date

  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