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