Ahh - so, the abbreviation timestamptz helps (since it threw an error when I tried to cast to:timestamp with time zone):
select to_timestamp(extract(epoch from '2014-12-01'::timestamptz));
to_timestamp
------------------------
2014-12-01 00:00:00-05
(1 row)
Works. And I suppose there is no implied garantee that "to_timestamp" and "extract epoch" are inverses of one another...But if nothing else, it still seems to me that "to_timestamp" and "extract(epoch)" are making different assumptions when TZ is not known. In other words, can we say that "extract epoch" assumes noTZ means noTZ, whereas "to_timestamp" assumes that noTZ means GMT.
On Tue, May 12, 2015 at 1:53 PM, Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx> wrote:
On May 12, 2015, at 11:33 AM, Robert Burgholzer <rburghol@xxxxxx> 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 time that is shifted the distance from local time to GMT (Example 1). I have a workaround for when I do data imports, in that if I create columns as "timestamp with timezone" and do the same conversion, they convert to and fro seemelessly (example 2).
>
> Thoughts on this? To me, it would seem intuitive that if you did not specify a timezone, the db would choose it's own local 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 PostgreSQL honors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.”
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 andtimestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval”
So you get number of seconds from UTC your local time in the call to epoch, essentially ignoring the specified ‘EST’ time zone. Then on converting back, it’s treated as seconds from UTC.
--
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
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/
