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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
