problem with timezone parsing

Karsten Hilbert <[email protected]> Sun, 15 Feb 2009 14:13:18 +0100

Newsgroups	gmane.comp.python.db.psycopg.devel
Message-ID	<[email protected]>

PostgreSQL (on Debian/Lenny) comes with a bunch of time
zones which psycopg2 cannot parse, such as Asia/Calcutta.
Attempts to read a "timestamp with time zone" column with
client timezone set to one of those zones throw an "unable
to parse time" exception. Strangely enough, Asia/Colombo,
which has the same utc offset (05:30:00) parses just fine.

The corresponding time zones can be found with the attached
script (make sure to adjust the DSN).

That script also reports another category of time zones
which PostgreSQL includes but cannot use ;-)   But that's
beyond the scope of psycopg2.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg

test-psycopg2-datetime-systematic.py (text/x-python, 1.7 KB)

# =======================================================================
# $Source: /sources/gnumed/gnumed/gnumed/client/testing/test-psycopg2-datetime-systematic.py,v $
__version__ = "$Revision: 1.1 $"
__author__  = "K.Hilbert <[email protected]>"
__license__ = 'GPL (details at http://www.gnu.org)'
# =======================================================================

print "testing psycopg2 date/time parsing"

import psycopg2
print "psycopg2:", psycopg2.__version__

#dsn = u'dbname=gnumed_v10 user=any-doc password=any-doc'
dsn = u'dbname=xxx user=xxx password=xxx'
dsn = u'you need to adjust this'
print dsn

conn = psycopg2.connect(dsn=dsn)

curs = conn.cursor()
cmd = u"""
select
	name,
	abbrev,
	utc_offset::text,
	case when
		is_dst then 'DST'
		else 'non-DST'
	end
from pg_timezone_names"""
curs.execute(cmd)
rows = curs.fetchall()
curs.close()
conn.rollback()

for row in rows:

	curs = conn.cursor()

	tz = row[0]
	cmd = u"set timezone to '%s'" % tz
	try:
		curs.execute(cmd)
	except StandardError, e:
		print "cannot use time zone", row
		#print " ", e
		curs.close()
		conn.rollback()
		continue

	cmd = u"""select '1920-01-19 23:00:00+01'::timestamp with time zone"""
	try:
		curs.execute(cmd)
		curs.fetchone()
	except StandardError, e:
		print "%s (%s / %s / %s) failed:" % (tz, row[1], row[2], row[3])
		print " ", e

	curs.close()
	conn.rollback()

conn.close()

# =======================================================================
# $Log: test-psycopg2-datetime-systematic.py,v $
# Revision 1.1  2009/02/10 18:45:32  ncq
# - psycopg2 cannot parse a bunch of settable time zones
#
# Revision 1.1  2009/02/10 13:57:03  ncq
# - test for psycopg2 on Ubuntu-Intrepid
#