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

Поиск
Список
Период
Сортировка
От Robert Burgholzer
Тема Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))
Дата
Msg-id CACT-NGKQRtBGKOGUMT_goxyKu=yym8ET1Q5my-yjfuWW++uXbA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))  (Scott Ribe <scott_ribe@elevated-dev.com>)
Список 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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Albe Laurenz
Дата:
Сообщение: Re: SSL and MD5 passwords
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))