Обсуждение: precision of epoch

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

That's as well the behaviour, when I use columns of a real table. I
seached the docs for a precsion value and even tried timestamp(6) as
well but with no success. What am I missing here ?

Gerhard

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

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

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