Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#46 interval (DateTimeDelta) returns maximal days

open
PgSQL (41)
5
2003-03-23
2003-03-22
Christian Heimes
No

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.

Discussion

1 2 > >> (Page 1 of 2)
  • Logged In: YES
    user_id=560817

    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
    information.
    >>> from mx import DateTime
    >>> t = DateTime.DateTimeDelta(7300)
    >>> t
    <DateTimeDelta object for '7300:00:00:00.00' at 819a1c0>
    >>> t.days
    7300.0
    >>>

    test=> SELECT * FROM testint;
    inter
    ----------
    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=> SET DATESTYLE TO NONEUROPEAN, GERMAN;
    SET VARIABLE
    test=> SELECT * FROM testint;
    inter
    ------------
    @ 20 days
    @ 20 years
    @ 2 mons
    @ 20 years
    @ 14 days
    (5 rows)

    mons and years are not supported by
    PgSQL.TypeCache.interval2datetimedelta

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

    Christian

     
    • assigned_to: nobody --> ghaering
     
    • assigned_to: ghaering --> ballie01
     
  • Logged In: YES
    user_id=163326

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

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

     
  • Logged In: YES
    user_id=560817

    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.

     
  • Logged In: YES
    user_id=163326

    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.

    Thoughts?

     
  • Logged In: YES
    user_id=560817

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

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

     
  • Logged In: YES
    user_id=163326

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

     
  • Logged In: YES
    user_id=560817

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

    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).
    http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/68205
    would be nice for the NULL value, too. The Null class
    supports postgres like behavior like NULL != False, NULL !=
    '', NULL != 0 ...

    Christian

     
  • Billy G. Allie
    Billy G. Allie
    2003-03-26

    Logged In: YES
    user_id=8500

    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;
    a
    -----------------
    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
    information.
    >>> 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;
    ?column?
    ----------
    t
    (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;
    ?column?
    ---------------------
    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;
    ?column?
    ----------
    365
    (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?

     
1 2 > >> (Page 1 of 2)