psycopg2 rounds unix time

[Steve Holden]

Luis P. Mendes wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hi,
> 
> I've inserted a couple hundred rows in a table in Postgres via psycopg2.
> 
> The first field of each row is a certain unix time (since epoch) when an
> event occured.
> When I try to access that database with psycopg2, I get rounded values
> for the unix time field.
> 
> example:
> unix time inserted at first row: 1138839839.64456
> unix time as retrieved in psql: 1.13884e+09
> unix time retrieved by psycopg2: 1138840000.0
> 
> Is this a bug?
> 
> I'm using:
> Python 2.3.5
> in a Debian Sarge box
> PostgreSQL 7.4.7
> psycopg2-2.0b6
> 
It could be your PostgreSQL. I'm running 8.0 on my Windows box and I 
can't get to the 7.3 on my Linux box. The 8.0 docs say the following 
about timestamps (which I presume you are using):

"""
Note: When timestamp values are stored as double precision 
floating-point numbers (currently the default), the effective limit of 
precision may be less than 6. timestamp values are stored as seconds 
before or after midnight 2000-01-01. Microsecond precision is achieved 
for dates within a few years of 2000-01-01, but the precision degrades 
for dates further away. When timestamp values are stored as eight-byte 
integers (a compile-time option), microsecond precision is available 
over the full range of values. However eight-byte integer timestamps 
have a more limited range of dates than shown above: from 4713 BC up to 
294276 AD. The same compile-time option also determines whether time and 
interval values are stored as floating-point or eight-byte integers. In 
the floating-point case, large interval values degrade in precision as 
the size of the interval increases.
"""

Otherwise, what data type *are* you using to store the Unix time?

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC                     www.holdenweb.com
PyCon TX 2006                  www.python.org/pycon/