Thread: Inconsistent behavior with TIMESTAMP WITHOUT and epoch

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.

Josh Berkus <josh@agliodbs.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

Tom,

> 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

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

Josh Berkus <josh@agliodbs.com> writes:
> 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.

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'))

> 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.

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

Tom,

> 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'))

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

> 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.

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