Skip site navigation (1) Skip section navigation (2)

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: (view raw or whole thread)
Lists: pgsql-bugs
Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Summary:  "epoch" does not produce a consistent behavior when cast as 

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

> 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


pgsql-bugs by date

Next:From: Koei KimDate: 2005-01-27 04:40:22
Subject: BUG #1441: casting problem when lower limit, ERROR out of range
Previous:From: Dennis HübnerDate: 2005-01-26 16:07:16
Subject: No connection to Server

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group