Thread: 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'::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

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

On Jan 24, 2014 11:52 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> The result is float8, so being good to about 16 digits is expected
> behavior.

I see. But how is it that the correct result is returned when I pass the
output of extract straight into to_timestamp?