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

[Robert Burgholzer <rburghol@xxxxxx>]

Ok, thanks a ton - I get it! (i think) let me try this out (maybe you said this all along:

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

Thanks!

On Tuesday, May 12, 2015, Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx> wrote:

On May 12, 2015, at 12:07 PM, Robert Burgholzer <rburghol@xxxxxx> 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@xxxxxxxxxxxxxxxx
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

Athletics: http://athleticalgorithm.wordpress.com/ 

Science: http://robertwb.wordpress.com/

Wine: http://reesvineyard.wordpress.com/