Re: precision of epoch

On Thu, Jun 14, 2007 at 04:40:12AM -0700, g(dot)hintermayer(at)inode(dot)at wrote:
> I'd like to convert timestamps without timezone to unix epoch values
> with at least microseconds resolution.
> but when i do e.g.:
> select extract (epoch from timestamp without time zone 'Thu 14 Jun
> 05:58:09.929994 2007');
>
> i get:
> 1181793489.92999
>
> so i loose the last digit. I'd expect 1181793489.929994

EXTRACT's return type is double precision, which isn't precise
enough to represent that many significant digits. Notice that
removing a digit from the beginning gives you another digit at
the end:

test=> SELECT '1181793489.929994'::double precision;
float8
------------------
1181793489.92999
(1 row)

test=> SELECT '181793489.929994'::double precision;
float8
------------------
181793489.929994
(1 row)

You could convert the epoch value to numeric but you'll have to use
a more complex expression; simply casting EXTRACT's result to numeric
won't work. One possibility might involve floor and to_char(value, '.US').

--
Michael Fuhr