Re: precision of epoch

On Jun 14, 2:18 pm, m(dot)(dot)(dot)(at)fuhr(dot)org (Michael Fuhr) wrote:
> On Thu, Jun 14, 2007 at 04:40:12AM -0700, g(dot)hinterma(dot)(dot)(dot)(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').
>

Your're righht, I did'nt take the 15 significant digit limitation of
double into account,

floor(extract(epoch from ts_column))||to_char(ts_column,'.US')

does the job, but since the limitation is generally in double
precision (in any language I process the result), I could as well use
just extract(epoch).

Thanks
Gerhard