psycopg2 rounds unix time

Discussion in 'Python' started by Luis P. Mendes, Feb 6, 2006.

  1. -----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-----
    Luis P. Mendes, Feb 6, 2006
    #1
    1. Advertising

  2. Luis P. Mendes

    Steve Holden Guest

    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/
    Steve Holden, Feb 6, 2006
    #2
    1. Advertising

  3. Re: psycopg2 rounds unix time [solved]

    -----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-----
    Luis P. Mendes, Feb 6, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. flamesrock
    Replies:
    8
    Views:
    430
    Hendrik van Rooyen
    Nov 24, 2006
  2. ASh
    Replies:
    10
    Views:
    2,358
    Anton Shishkov
    Mar 31, 2010
  3. mrdrew
    Replies:
    5
    Views:
    2,650
    Dennis Lee Bieber
    Apr 5, 2010
  4. Brian

    Real time converted to Unix time

    Brian, Aug 29, 2003, in forum: Perl Misc
    Replies:
    1
    Views:
    371
    James Willmore
    Aug 29, 2003
  5. eli m

    C++ rounds up my decimals

    eli m, Apr 10, 2013, in forum: C++
    Replies:
    10
    Views:
    360
    Stuart
    Apr 11, 2013
Loading...

Share This Page