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

Inconsistent behavior with TIMESTAMP WITHOUT and epoch

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Inconsistent behavior with TIMESTAMP WITHOUT and epoch
Date: 2005-01-25 00:31:41
Message-ID: 200501241631.41982.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Summary:  "epoch" does not produce a consistent behavior when cast as 
TIMESTAMP WITHOUT TIMEZONE
Severity:  Annoyance
Tested On:  7.4.6, 8.0b4
Example:

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

this value is actually local time, not GMT time, as one might expect from 
TIMESTAMP WITHOUT TIMEZONE.  We see this problem when we try to reverse the 
process:

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

btw, to reenforce the above:

webmergers2=> select extract(epoch from '2004-12-01 00:00 GMT'::TIMESTAMPTZ);
 date_part
------------
 1101859200

thus, EXTRACT(epoch) as TIMESTAMP-NO-TZ produces local time, and CAST(epoch AS 
timestamp-no-tz) produces GMT.   This is inconsistent; it should do either 
local time or GMT for both.

--Josh Berkus

P.S. if anyone is wondering why I'm doing epoch with timestamp-no-tz it's for 
a calendaring application which exists on 2 servers in two different time 
zones, and all I really want is the date.







Responses

pgsql-bugs by date

Next:From: Neil ConwayDate: 2005-01-25 00:34:54
Subject: Re: plperl trigger crash backend 8.0.rc4
Previous:From: Tom LaneDate: 2005-01-25 00:11:06
Subject: Re: Insecure temporary file usage in developer/build tools

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