Hi
I am trying to use active record to copy some data. One of the entry I
am copying is type interval (Postgres); however, it lose some precision
after I copied that column.
Here is what I did:
@newData.elapsedtime = @oldData.elapsedtime
@newData.save
Result:
both displayed as 00:00:02.453 in the table, however, if I use the
following command to convert its value into float
( SELECT date_part(‘epoch’::text, [table].elapsedtime) AS date_part) AS
elapsedtime_sec
The original --> 2.4529983024597
After copy --> 2.453
Is there any way to keep the original value?
Thank you very much!
On Jul 26, 2006, at 5:06 , Victor F. wrote:
One of the entry I
am copying is type interval (Postgres); however, it lose some
precision
after I copied that column.
The original --> 2.4529983024597
After copy --> 2.453
Is there any way to keep the original value?
I’m not familiar with how Rails, the Postgres adapter, or your code
handle the conversion between the Postgres interval type and the
corresponding Ruby object, so I can’t say whether or not it’s losing
precision there. My guess is that this is where the problem is as
well, so what follows is just a bit of explanation of Postgres
internals.
On the Postgres server side, it looks like the server was not
compiled with the --enable-integer-datetimes flag. With --enable-
integer-datetimes you’ll get microsecond precision. You’re showing
much more that that, which means that datetimes (e.g., timestamps and
intervals) are stored internally as floats. That precision you see
there is not guaranteed and prone to all of the interesting aspects
of floating point math.
If you need up to microsecond precision, see if you can’t get your
server to be compiled with --enable-integer-datetimes. If you need
precision greater than that, perhaps store number of seconds in a
numeric column.
Don’t know if this helps, but perhaps it enlightens a bit. I’m
interested to see if someone pipes up about how a Postgres interval
is translated to Ruby and back. (I suppose I could look a the source
myself, but I’m not as of yet sufficiently motivated 
)
Michael G.
grzm seespotcode net
Michael G. wrote:
I’m not familiar with how Rails, the Postgres adapter, or your code
handle the conversion between the Postgres interval type and the
corresponding Ruby object, so I can’t say whether or not it’s losing
precision there. My guess is that this is where the problem is as
well, so what follows is just a bit of explanation of Postgres
internals.
On the Postgres server side, it looks like the server was not
compiled with the --enable-integer-datetimes flag. With --enable-
integer-datetimes you’ll get microsecond precision. You’re showing
much more that that, which means that datetimes (e.g., timestamps and
intervals) are stored internally as floats. That precision you see
there is not guaranteed and prone to all of the interesting aspects
of floating point math.
If you need up to microsecond precision, see if you can’t get your
server to be compiled with --enable-integer-datetimes. If you need
precision greater than that, perhaps store number of seconds in a
numeric column.
Don’t know if this helps, but perhaps it enlightens a bit. I’m
interested to see if someone pipes up about how a Postgres interval
is translated to Ruby and back. (I suppose I could look a the source
myself, but I’m not as of yet sufficiently motivated )
Michael G.
grzm seespotcode net
Thank you Michael for your reply
I did a bit of research, but couldn’t find anything useful so far.
Victor