[Pypgsql-devel] [ pypgsql-Bugs-708002 ] interval (DateTimeDelta) returns maximal days

[SourceForge.net <no...@so...>]

Bugs item #708002, was opened at 2003-03-22 14:12
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=708002&group_id=16528

Category: PgSQL
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Christian Heimes (tiran)
Assigned to: Billy G. Allie (ballie01)
Summary: interval (DateTimeDelta) returns maximal days

Initial Comment:
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.



----------------------------------------------------------------------

>Comment By: Christian Heimes (tiran)
Date: 2003-03-23 22:12

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

----------------------------------------------------------------------

Comment By: Gerhard Häring (ghaering)
Date: 2003-03-23 21:55

Message:
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.

----------------------------------------------------------------------

Comment By: Christian Heimes (tiran)
Date: 2003-03-23 21:50

Message:
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?

----------------------------------------------------------------------

Comment By: Gerhard Häring (ghaering)
Date: 2003-03-23 21:39

Message:
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?

----------------------------------------------------------------------

Comment By: Christian Heimes (tiran)
Date: 2003-03-23 20:26

Message:
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.

----------------------------------------------------------------------

Comment By: Gerhard Häring (ghaering)
Date: 2003-03-23 19:57

Message:
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.

----------------------------------------------------------------------

Comment By: Christian Heimes (tiran)
Date: 2003-03-22 16:53

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

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=708002&group_id=16528