Postgresql to_timestamp returning different date when wrapping extract()

Question

An import script was written slightly wrong, leading to timestamps being inserted a factor of 1000 out. Using to_timestamp together with extract() however leads to dates about a month out, even though the intermediate numbers and conversions look to be correct.

1)
select start_time from matches order by match_id limit 1;

  | timestamp without time zone
----------------------
1 | 1970-01-16 11:29:18.352


2)
select (extract(epoch from start_time) * 1000)
  from matches order by match_id limit 1;

  | double precision
----------------------
1 | 1333758352


3)
select to_timestamp(1333758352) at time zone 'UTC';

  | timestamp without time zone
----------------------
1 | 2012-04-07 00:25:52


4)
select (to_timestamp(extract(epoch from start_time) * 1000) at time zone 'UTC')
  from matches order by match_id limit 1;

  | timestamp without time zone
----------------------
1 | 2012-05-18 16:25:52

1) shows the current data in a timestamp column, 2) and 3) shows the correct number and conversion being performed, but put together in one query in 4) the date ends up offset by over a month.

I'm kind of stumped on what the issue could be, as the components work individually, and simple math inside a to_timestamp also works in other situations I tried.

Reproducing (PostgreSQL 9.3.1):

create table test_table ( t timestamp );
insert into test_table VALUES ('1970-01-16 11:29:18.352');
select (extract(epoch from t) * 1000) from test_table;
select to_timestamp(1333758352) at time zone 'UTC';
select (to_timestamp(extract(epoch from t) * 1000) at time zone 'UTC') from test_table;

SqlFiddle: http://sqlfiddle.com/#!15/12f6f/1/0

On SqlFiddle it works on 8.4, but fails on 9.3.

Resolution and cause

The naive approach here hits an edge case where we end up multiplying the timezone offset of one hour along with the original date. So the result is 1000 hours offset (due to my being in GMT). Casting the non UTC time to have a time zone drops that GMT conversion letting the odd multiplication of a date proceed as expected.

So, extract(epoch from t::timestamp with time zone) when doing raw math on the result and then putting it back into a timestamp.

"PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 64-bit" on Windows 7 64bit. I'm adding reproduction steps to the question. — spent
– spent, Commented Dec 6, 2013 at 12:16

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-12-06 12:17:20Z

2

There's definitely a difference in behavior between 8.x and 9.x versions. (fiddle)

select 
    start_time
  , extract(epoch from start_time) * 1000 as epoch_start_time
  , to_timestamp(1337358352) at time zone 'UTC' as to_timestamp_int
  , to_timestamp(extract(epoch from start_time) * 1000) at time zone 'UTC' as to_timestamp_expression
from matches;

8.x shows your behavior. 9.x shows my "can't reproduce" behavior. (You can switch versions in SQLfiddle's toolbar.)

answered Dec 6, 2013 at 12:17

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

spent Over a year ago

Interesting that my own reproduction works on 8.4 but doesn't on 9.3.

Mike Sherrill 'Cat Recall' Over a year ago

Could it be related to this bug? github.com/postgres/postgres/commit/…

spent Over a year ago

Appears so! Using extract(epoch from t::timestamp with time zone) gives the right answer.

Collectives™ on Stack Overflow

Postgresql to_timestamp returning different date when wrapping extract()

Resolution and cause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Resolution and cause

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related