It seems that extract(epoch ..) does not preserve the entire precision of
the passed-in timestamp. In the following example, it rounds to the nearest
0.00001:
> select extract(epoch from '2014-01-21 16:10:30.403777'::timestamp);
date_part
------------------
1390320630.40378
(1 row)
Just to be sure, I convert this value back to timestamp and it is still
rounded (and incorrect):
> select to_timestamp(1390320630.40378);
to_timestamp
------------------------------
2014-01-21 11:10:30.40378-05
(1 row)
Oddly, when the result of extract(epoch) is passed directly back into
to_timestamp, the result is correct!
> select to_timestamp(extract(epoch from '2014-01-21 16:10:30.403777'::time=
stamp));
to_timestamp
-------------------------------
2014-01-21 11:10:30.403777-05
(1 row)
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway =B7 Suite 201 =B7 Atlanta, GA 30339
=93Quality is not an act, it is a habit.=94 =97 Aristotle