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