psycopg2 rounds unix time

[Luis P. Mendes]

-----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

Luis P. Mendes
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFD5q7kHn4UHCY8rB8RAvh0AJ4nr6239OT6vweUVEF3Htq8smaCJQCgoVnY
NZXZdAyq9UmNXdlOxtwN2y8=
=ZawE
-----END PGP SIGNATURE-----

 

[Steve Holden]

-----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

 

[Luis P. Mendes]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


|> 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

| 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?

Thank you for your answer.

In python, I use float data type to deal with time values (from
time.time()), for example.

In Postgresql, the unix time field was set up as real. I've changed it
to double precision and it runs fine, now.


Luis P. Mendes
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFD50nTHn4UHCY8rB8RAkUUAJ9Pby8S5do7c7qMYjC6t222eDyG8wCfTJOR
aGMlkq86dN1juXUjBMgRuGQ=
=nnGl
-----END PGP SIGNATURE-----