Fun With Timestamps in PostgreSQL

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).  All examples are running on a PostgreSQL install configured for EST (postgresql.conf: “timezone = ‘US/Eastern'”).

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: Timezone shifts 5 hours (distance from EST to GMT).
select to_timestamp(extract(epoch from ‘2014-12-01 EST’::timestamp));           to_timestamp
 2014-11-30 19:00:00-05
(1 row)
Example 2: Timezone conversion and reconversion yields same result.
create temp table tmp_tstest(tstime timestamp with time zone);
insert into tmp_tstest values (‘2014-12-01’);
 select * from tmp_tstest ;
 2014-12-01 00:00:00-05
select to_timestamp(extract (epoch from tstime)) from tmp_tstest;
 2014-12-01 00:00:00-05
(1 row)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s