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: 17708.1106766289@sss.pgh.pa.us (view raw or flat)
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

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-2014 The PostgreSQL Global Development Group