#46 interval (DateTimeDelta) returns maximal days

PgSQL (41)

I'm working on a pyPgSQLDA for Zope3 (see
http://cvs.zope.org/zopeproducts/pypgsqlda/\). I have
noticed a problem mit time intervals.

INSERT INTO testinterval VALUES('20 years');
INSERT INTO testinterval VALUES('20 days');

pyPgSQL returns 20.0 days for both statements, what is
really strange becauce the interval type is 12bytes
long and supports more than a billion years.


  • Christian Heimes

    Logged In: YES

    I did some more testing:

    $ python2.2
    Python 2.2.2 (#1, Jan 18 2003, 10:18:59)
    [GCC 3.2.2 20030109 (Debian prerelease)] on linux2
    Type "help", "copyright", "credits" or "license" for more
    >>> from mx import DateTime
    >>> t = DateTime.DateTimeDelta(7300)
    >>> t
    <DateTimeDelta object for '7300:00:00:00.00' at 819a1c0>
    >>> t.days

    test=> SELECT * FROM testint;
    20 days
    20 years
    2 mons
    20 years
    14 days
    (5 rows)
    These are the values from the database for: 20 days, 20
    years, 2 month, 2 decades, 2 weeks

    test=> SELECT * FROM testint;
    @ 20 days
    @ 20 years
    @ 2 mons
    @ 20 years
    @ 14 days
    (5 rows)

    mons and years are not supported by

    Maybe you could convert them to the following values:
    1 Month -> 30 days
    3 Month -> 91 days (?)
    6 Month -> 182 days (?)
    12 Month -> 1 year
    20 Month -> 1 year and 8 month
    1 year -> 365 days
    4 years -> 1461 days (4* 1 years + 1 day) (?)
    400 years -> 146099 days (100 * 4 years - 1 day) (?)
    2000 years -> 730496 (5* 400 years +1 day) (?)


  • Gerhard Häring

    Gerhard Häring - 2003-03-23
    • assigned_to: nobody --> ghaering
  • Gerhard Häring

    Gerhard Häring - 2003-03-23
    • assigned_to: ghaering --> ballie01
  • Gerhard Häring

    Gerhard Häring - 2003-03-23

    Logged In: YES

    Assigning to Billy, because I just don't get it:

    This is WEIRD. It seems PostgreSQL remembers the years and
    months. I always thought it might convert everything into
    some base, say seconds internally.

    Looks like it doesn't.

    This might sound like a provocation, but here it goes: if
    PostgreSQL isn't smart enough to convert, say a year to 365
    days, I don't think we should try to be so smart.

    A DateTimeDelta from mxDateTime is an exact timespan, not a
    remember-the-user-input thingie like PostgreSQL's INTERVAL
    seems to be.

    So it's not possible to do an exact conversion from INTERVAL
    to DateTimeDelta in all cases. Where it is possible, I think
    it currently works.

    My solution for the application programmer is to only use
    days/hours/seconds when inserting values into an INTERVAL

    I'd be glad to hear your opinion on this.

  • Christian Heimes

    Logged In: YES

    I studied my Practical Postgres book from o'reily and found
    an instresting information. A 'postgres year' are 365.24 days.

    My opinion is:
    * convert a year to 365.24 days
    * convert a moth to 365.24/12 days

    This is not an excat translation from year to days, but
    better then converting the string "20 years" to 20 days. I
    think it's the best workaround to keep backward
    compatibility to existing data.

    * put a HUGHE warning in the readme of pypgsql

    So programmers are informed about the problems that could
    accure using years and month statements in INTERVAL.

  • Gerhard Häring

    Gerhard Häring - 2003-03-23

    Logged In: YES

    Alternatively, we could look wether only days/hours/... are
    used in the string, then use the mxDateTime function to
    parse it. Otherwise, raise an exception.

    At least this wouldn't fail silently, returning a wrong value.


  • Christian Heimes

    Logged In: YES

    I think the best way to get rid of this silly problems with
    date and time would be changing the orbit of earth and using
    the Napoleon Calender. Who wants to write a proposal how to
    do this?

    Why not supporting both ways so the user can chocie wether
    he wants to convert years to 365.24 days or get an exception?

  • Gerhard Häring

    Gerhard Häring - 2003-03-23

    Logged In: YES

    This sounds like the best option.

    If you could provide a patch, that'd be great :-)

    This also sounds like another change that'd justify a bugfix

  • Christian Heimes

    Logged In: YES

    I'll see what I can do. But at first I have to learn for

    Maybe you are interested in my ideas of mapping some other
    postgres specifiy types to python. I had first successes of
    converting inet, cidr and the geometric types to lists and
    tuples (see cvs.zope.org/zopeproducts/pypgsqlda).
    would be nice for the NULL value, too. The Null class
    supports postgres like behavior like NULL != False, NULL !=
    '', NULL != 0 ...


  • Billy G. Allie

    Billy G. Allie - 2003-03-26

    Logged In: YES

    I belive the original problem of 20 days and 20 years
    returning the same value is fixed in the latest PgSQL.py
    from CVS:

    bga=# \d dtest
    Table "public.dtest"
    Column | Type | Modifiers
    a | interval |

    bga=# select * from dtest;
    20 years
    20 days
    7304 days 20:24
    (3 rows)

    bga=# \q
    $ python
    Python 2.2.2 (#7, Nov 27 2002, 17:10:05) [C] on openunix8
    Type "help", "copyright", "credits" or "license" for more
    >>> from pyPgSQL import PgSQL
    >>> cx = PgSQL.connect(password='**********')
    >>> cu = cx.cursor()
    >>> cu.execute('select * from dtest')
    >>> r = cu.fetchall()
    >>> for i in r:
    ... print i
    [<DateTimeDelta object for '7304:20:24:00.00' at 81fc470>]
    [<DateTimeDelta object for '20:00:00:00.00' at 81fa8b0>]
    [<DateTimeDelta object for '7304:20:24:00.00' at 81fc5b0>]

    But PostgreSQL intervals seem wierd to me. For examples:

    An interval of 1 year is equal to an interval of 360 (not
    365) days
    bga=# select '1 year'::interval = '360 days'::interval;
    (1 row)

    but adding an interval of 1 year to a date does the right thing:
    bga=# select '7/11/1954'::date + '1 year'::interval;
    1955-07-11 00:00:00
    (1 row)

    Yet subtracting the dates give a value of 365:
    bga=# select '7/11/1955'::date - '7/11/1954'::date;
    (1 row)

    This is all very strange to me. Futher investigation give:

    1 year = 360 days
    1 month = 30 days
    1 month = 4 weeks 2 days
    1 week = 7 days

    which doesn't seem to bear much relationship to the actual
    number of days between 2 dates. It seems to me that the
    only way to accurately represent a PostgreSQL interval is to
    do what PostgreSQL does and remember the original user
    input, i.e. have an object with attributes for years,
    months, weeks, days, hours, minutes, seconds. Adding and
    subtracting the interval from a date would then involve
    adding/subtracting the parts to/from the date.

    your thoughts?

  • Gerhard Häring

    Gerhard Häring - 2003-03-26

    Logged In: YES

    Do we really want to invent our own INTERVAL type?

    What about inventing one like this:

    class BrokenInterval(str): pass

    This could be used for the broken (IMO) intervals. All
    others could then still be returned as DateTimeDelta.

    Just a thought, because I'm wary of making the TODO list
    grow much more ;-)


Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

No, thanks