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.







---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Tom,

Darn. I missed that discussion, I'd have argued with Thomas (not that I ever
*won* such an argument ...)

The problem with the current functionality is that it makes it impossible to
get a GMT Unix timestamp out of a TIMESTAMP WITHOUT TIME ZONE without string
manipulation. And for an application where you want the timestamps to be
location-agnostic (such as this one, with servers on east and west coasts,
and some talk about London), you want your timestamps stored as GMT.

However, having changed it in 7.3, I agree that we'll just cause trouble
changing it back.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Josh Berkus <josh (AT) agliodbs (DOT) com> writes:
How so? If you think that the timestamp-without-zone is relative to GMT
rather than your local zone, you say something like
extract(epoch from (timestampvar AT TIME ZONE 'GMT'))

Quite honestly, you should be using timestamp WITH time zone for such an
application anyway. The timestamp without zone datatype is very
strongly biased towards the assumption that the value is in your local
timezone, and if you've actually got multiple possible settings of
TimeZone then it's simply a great way to shoot yourself in the foot.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom,

Ah, that didn't seem to work before. I must have done the parens wrong.

Well, I was thinking about this on the way to my office this AM, and realized
that there's a fundamental gulf between timestamp-as-real-moment-in-time (the
SQL timestamp and postgres timestamp) and timestamp-as-mark-on-the-calendar
(what I'm dealing with), and that my trouble stems from trying to coerce the
first into the second.

Maybe it's time to hack a datatype ...

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend