On Thu, Jun 14, 2007 at 04:40:12AM -0700, g.hintermayer@inode.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