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

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

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

- to_timestamp translates into the local TZ Always.

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

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

On Tuesday, May 12, 2015, Scott Ribe <scott_ribe@elevated-dev.com> wrote:

On May 12, 2015, at 12:07 PM, Robert Burgholzer <rburghol@vt.edu> wrote:
>
> But if nothing else, it still seems to me that "to_timestamp" and "extract(epoch)" are making different assumptions when TZ is not known.

Not really, it’s just that by default the result of to_timestamp is *displayed* in your local zone.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice

--
Robert W. Burgholzer
 'Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity.'  - Charles Mingus