Обсуждение: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))

On May 12, 2015, at 11:33 AM, Robert Burgholzer <rburghol@vt.edu> wrote:
>
> In postgresql 9.3 I am running into what I consider counterintuitive behavior when I convert something to a Unix
epoch,then back from a timestamp without timezone.  Calling "to_timestamp(extract (epoch from timestamp))" returns a
timethat is shifted the distance from local time to GMT (Example 1).  I have a workaround for when I do data imports,
inthat if I create columns as "timestamp with timezone" and do the same conversion, they convert to and fro seemelessly
(example2).   
>
> Thoughts on this?  To me, it would seem intuitive that if you did not specify a timezone, the db would choose it's
ownlocal timestamp as the timezone. 

From the documentation on date/time data types:

"Note: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and
PostgreSQLhonors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a
PostgreSQLextension.” 

Then from date/time functions:

"epoch
For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date
andtimestampvalues, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number
ofseconds in the interval” 

So you get number of seconds from UTC your local time in the call to epoch, essentially ignoring the specified ‘EST’
timezone. Then on converting back, it’s treated as seconds from UTC. 

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

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

On May 12, 2015, at 5:49 PM, Robert Burgholzer <rburghol@vt.edu> wrote:
>
> to_timestamp translates into the local TZ Always.

No, it translates to UTC, but timestamps by default display in your local timezone. Look at your example, the result of
to_timestampis midnight UTC as expected, which in your time zone is 19:00. 

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

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