Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))

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