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.92999so 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.92999so 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