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

From: Robert Burgholzer <rburghol(at)vt(dot)edu>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))
Date: 2015-05-12 17:33:53
Message-ID: CACT-NGKQRtBGKOGUMT_goxyKu=yym8ET1Q5my-yjfuWW++uXbA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Example 1:
postgresql.conf: "timezone = 'US/Eastern'"
select to_timestamp(extract(epoch from '2014-12-01 EST'::timestamp));
to_timestamp
------------------------
2014-11-30 19:00:00-05
(1 row)

Example 2:

create temp table tmp_tstest(tstime timestamp with time zone);
insert into tmp_tstest values ('2014-12-01');
select * from tmp_tstest ;
tstime
------------------------
2014-12-01 00:00:00-05

select to_timestamp(extract (epoch from tstime)) from tmp_tstest;
to_timestamp
------------------------
2014-12-01 00:00:00-05
(1 row)

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2015-05-12 17:44:26 Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))
Previous Message Albe Laurenz 2015-05-12 07:55:14 Re: SSL and MD5 passwords