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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2005-01-25 00:34:54 | Re: plperl trigger crash backend 8.0.rc4 |
Previous Message | Tom Lane | 2005-01-25 00:11:06 | Re: Insecure temporary file usage in developer/build tools |