bug in interval2DateTimeDelta() method of TypeCast

Karsten Hilbert <[email protected]> Wed, 12 May 2004 13:15:47 +0200
Newsgroups gmane.comp.python.db.pypgsql.user,gmane.comp.gnu.medical.devel
Message-ID <[email protected]> 
Dear pyPgSQL developers,

pyPgSQL assumes that PostgreSQL will not issue "mons" parts in
interval output. It does, however, if age() is used in
queries. The result of age() is, of course, not safe for
post-processing and thus shouldn't really be used. However,
pyPgSQL also works around the unsafe practice of returning
"years" parts in intervals (yes, they are 365.2425 days in
most cases but not always, think leap years).

So, to at least make pyPgSQL *work* with intervals including
"mons" I fixed interval2DateTimeDelta(). Patch attached. I
also added a comment explaining why date-date should be used
instead of age() if one intends to post-process the interval.

Please consider for inclusion.

Regards,
Karsten Hilbert, MD
GnuMed i18n coordinator
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
ZZ-pyPgSQL-interval2mxDateTimeDelta-bugfix.py (text/plain, 1.7 KB) 
def interval2DateTimeDelta(self, s):
	"""Parses PostgreSQL INTERVALs.
	The expected format is [[[-]YY years] [-]DD days] [-]HH:MM:SS.ss"""
	parser = DateTime.Parser.DateTimeDeltaFromString

	ydh = s.split()
	ago = 1

	result = DateTimeDelta(0) 

	# Convert any years using 365.2425 days per year, which is PostgreSQL's
	# assumption about the number of days in a year.
	if len(ydh) > 1:
		if ydh[1].lower().startswith('year'):
			result += parser('%s days' % ((int(ydh[0]) * 365.2425),))
			ydh = ydh[2:]

	# Convert any months using 30 days per month, which is PostgreSQL's
	# assumption about the number of days in a months IF it doesn't
	# know the end or start date of the interval. If PG DOES know either
	# date it will use the correct length of the month, eg 28-31 days.
	# However, at this stage we have no way of telling which one it
	# was. If you want to work with accurate intervals (eg. post-processing
	# them) you need to use date1-date2 syntax rather than age(date1, date2)
	# in your queries.
	#
	# This is per discussion on pgsql-general:
	#  http://www.spinics.net/lists/pgsql/msg09668.html
	# Google for: >>>"interval output format" available that removes ambiguity<<<
	#
	# Note: Should a notice be provided to the user that post-processing
	#       year/month intervals is unsafe practice ?
	if len(ydh) > 1:
		if ydh[1].lower().startswith('mon'):
			result += parser('%s days' % ((int(ydh[0]) * 30),))
			ydh = ydh[2:]

	# Converts any days and adds it to the years (as an interval)
	if len(ydh) > 1:
		if ydh[1].lower().startswith('day'):
			result += parser('%s days' % (ydh[0],))
			ydh = ydh[2:]

	# Adds in the hours, minutes, seconds (as an interval)
	if len(ydh) > 0:
		result += parser(ydh[0])

	return result
signature.asc (application/pgp-signature, 248 B) 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: Weitere Infos: siehe http://www.gnupg.org

iEYEAREDAAYFAkCiB2IACgkQeLmp+eQHE0ZZbgCg68qShiIb2L66L1KKIM6X75AD
sJEAn3cngP472R3yf9zTXYVksLqVV0e4
=uAn5
-----END PGP SIGNATURE-----