Rounding error on extract(epoch ..)
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'::timestamp));
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 · Suite 201 · Atlanta, GA 30339
“Quality is not an act, it is a habit.” — Aristotle
Moshe Jacobson <moshe@neadwerx.com> writes:
It seems that extract(epoch ..) does not preserve the entire precision of
the passed-in timestamp.
The result is float8, so being good to about 16 digits is expected
behavior.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs