Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp)) - Mailing list pgsql-admin

From Albe Laurenz
Subject Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))
Date
Msg-id A737B7A37273E048B164557ADEF4A58B3660F567@ntex2010i.host.magwien.gv.at
Whole thread Raw
In response to Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))  (Robert Burgholzer <rburghol@vt.edu>)
List pgsql-admin
Robert Burgholzer wrote:
> Ok, thanks a ton - I get it! (i think) let me try this out (maybe you said this all along:

Almost...

> - an epoch is by definition in GMT - it can't be otherwise

An epoch is not in a time zone, it is an interval measured in seconds.
No time zone information in that.

> - an epoch is translated from its source TZ if TZ is specified, otherwise it's assumed GMT

I cannot follow here.

> - to_timestamp translates into the local TZ Always.

to_timestamp has a result type of "timestamp with time zone", so it
will always return a timestamp in UTC (that is upon display converted to your
local time zone).

> - therefore, the only time TStamp->Epoch->Tstamp results in identity is when the TZ of the original
> Tstamp is in the TZ specified in postgresql.conf?

Not quite, if you are refering to your original query
   select to_timestamp(extract(epoch from '2014-12-01 EST'::timestamp));

The cast of '2014-12-01 EST' (a string!) to a "timestamp without time zone"
discards the time zone information.
The epoch is then calculated as seconds since 1970-01-01 00:00:00 *local time*.
to_timestamp adds this to 1970-01-01 00:00:00 *UTC*.

So the result will look the same as the original string only if
a) your local time zone is identical to UTC and
b) the original string specifies a time zone identical with UTC.

> The "always" part of to_timestamp seems a tad limiting, but I dig, "+ interval" is my friend.

It depends on your problem.

You said that you can use fields of type "timestamp with time zone" as a workaround,
but I don't think that is a workaround, rather that it is the solution.

"Timestamp with time zone" is almost always the correct data type to model a
point in time.  It is hard for me to come up with a use case where "timestamp
without time zone" would be appropriate.
I guess that the reason that many people get away with using it is that all
their systems and data only refer to one time zone.

Yours,
Laurenz Albe

pgsql-admin by date:

Previous
From: Scott Ribe
Date:
Subject: Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))
Next
From: Mark Steben
Date:
Subject: Fwd: Two questions I would like insight on