473,320 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

psycopg2 rounds unix time

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

iD8DBQFD5q7kHn4UHCY8rB8RAvh0AJ4nr6239OT6vweUVEF3Ht q8smaCJQCgoVnY
NZXZdAyq9UmNXdlOxtwN2y8=
=ZawE
-----END PGP SIGNATURE-----
Feb 6 '06 #1
2 2973
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/

Feb 6 '06 #2
-----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

iD8DBQFD50nTHn4UHCY8rB8RAkUUAJ9Pby8S5do7c7qMYjC6t2 22eDyG8wCfTJOR
aGMlkq86dN1juXUjBMgRuGQ=
=nnGl
-----END PGP SIGNATURE-----
Feb 6 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: mvanier | last post by:
There was a thread a while back dealing with an error message the psycopg2 Postgres interface gives when trying to convert some mxDateTime values: "can't adapt". The answer given was that psycopg2...
1
by: Maxim Sloyko | last post by:
Hello, clp and all people reading it! Recently I was porting my (small) app from psycopg to psycopg2 (they got me with this "2"). I read, that psycopg2 supports all features of psycopg and plus...
2
by: Luis P. Mendes | last post by:
Hi, I've installed psycopg2 under Slacware 11.0 along with PostgreSQL 8.2.4. When I run the python shell I get the following error: lupe@lince ~$ python Python 2.4.3 (#1, Jul 26 2006,...
2
by: Jon Clements | last post by:
Hi All. I'm using psycopg2 to retrieve results from a rather large query (it returns 22m records); unsurprisingly this doesn't fit in memory all at once. What I'd like to achieve is something...
0
by: David Michael Schruth, | last post by:
Hi, I am sort of in a jam here. I am using the PsycoPG2 library to read data out of a windows XP based PostGIS / PostGreSQL database but I am apparently unable to write (update or insert) even...
1
by: George Sakkis | last post by:
I have a simple DB table that stores md5 signature pairs: Table "public.duplicate" Column | Type | Modifiers ----------+-------+----------- sig | bytea | not null orig_sig | bytea | not...
0
by: RossGK | last post by:
I've been using pydev for a short while successfully, and Django with postgresql as well. psycopg2 is part of that behind the scenes I would imagine, to make django work. Now I'm trying to use...
5
by: Thomas Guettler | last post by:
Hi, I discovered this: import psycopg2 connection=psycopg2.connect("dbname='...' user='...'") cursor=connection.cursor() cursor.execute('''SELECT '%' ''') # Does not fail...
0
by: Philip Semanchuk | last post by:
On Oct 13, 2008, at 11:52 PM, raj.indian.08@gmail.com wrote: The psycopg mailing list would be a better place to ask. I've never installed psycopg on Windows but this issue comes up from...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.