Menu
▾
▴
#46 interval (DateTimeDelta) returns maximal days
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.
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
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
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?
Logged In: YES
user_id=163326
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 ;-)