From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "'Robert Burgholzer *EXTERN*'" <rburghol(at)vt(dot)edu>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp)) |
Date: | 2015-05-13 08:00:49 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B3660F567@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Steben | 2015-05-13 16:21:48 | Fwd: Two questions I would like insight on |
Previous Message | Scott Ribe | 2015-05-12 23:58:35 | Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp)) |