BUG #5470: EXTRACT(epoch from ...) missing last digit
The following bug has been logged online:
Bug reference: 5470
Logged by: Randy Solomonson
Email address: pgsqlbugs@solomonson.com
PostgreSQL version: 8.4.2
Operating system: Red Hat 4.1.2-46 (64-bit)
Description: EXTRACT(epoch from ...) missing last digit
Details:
When converting from Timestamp with Time zone to seconds since epoch, the
last digit of the fraction of a second is missing.
Try the following query:
SELECT x,EXTRACT(epoch from x),EXTRACT(microseconds from x)/1000000
FROM (SELECT '2010-05-24 11:38:01.701845'::timestamp x)t
Here are the results:
x | date_part | ?column?
----------------------------+------------------+----------
2010-05-24 11:38:01.701845 | 1274719081.70184 | 1.701845
Notice the date_part ends in 70184 and drops the sixth digit (a "5" in this
case). Oddly enough, the last digit was also not rounded up. However, it
is rounded up in some cases (eg .538505)
"Randy Solomonson" <pgsqlbugs@solomonson.com> writes:
When converting from Timestamp with Time zone to seconds since epoch, the
last digit of the fraction of a second is missing.
The result of extract() is float8, so you can't really expect it to
have more than about 15 digits of precision.
regards, tom lane