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

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(at)elevated-dev(dot)com>
wrote:

> On May 12, 2015, at 11:33 AM, Robert Burgholzer <rburghol(at)vt(dot)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 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(at)elevated-dev(dot)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
Athletics: http://athleticalgorithm.wordpress.com/
Science: http://robertwb.wordpress.com/
Wine: http://reesvineyard.wordpress.com/