[PyGreSQL] pygresql - timestamp handling

[Christoph Zwerschke]

> I'm attempting to find out the reason for using a string when
> representing a DATETIME column.  I was expecting a DateTime object to
> be returned so that date operations could be performed on that column.
>  I've found several references in the list archives that indicate this
> is intentional.

You're right. I also noticed DateTime support is pretty rudimentary and 
inconsistent. pgdb cares about DateTime only in the context of input 
parameters, but for output, it just returns strings.

I'm not the one who designed the DB API and pgdb, but I guess the reason 
is that 1) the DATETIME pgdbType has been defined as a conglomeration of 
different PostGreSQL data types (like date, time, timestamp and even 
interval), so the "typecast" method of pgdbTypeCache does not know into 
what (date, time, datetime or timedelta object) to convert a DATETIME 
value, and 2) even if the exact date/time type would be clear, it still 
would be unclear how to convert the string because PostGreSQL may return 
date/time values in different format, depending on the locale and 
datestyle parameters.

It's pretty clear how to solve problem 1) by adding additional types and 
making case distinctions either in the get_type_array() function in 
pgmodule.c or in the typecast method of pgdbTypeCache in pgdb.py.

However, I'm not sure how to solve 2) or if it can be solved at all. For 
instance, if datestyle='German', PostgreSQL will return '18.11.2005' as 
the current date, but if datestyle is not set, it will return 
'2005-11-18'. So PyGreSQL would have to check the value of datestyle and 
lc_time in order to interpret the date, or it would have to interfere 
and set a fixed datestyle for the session which may lead to other 
problems. It will be even more difficult for intervals, where PostgreSQL 
will return data like '@ 1 day' or maybe '@ 1 tag' in German, depending 
on the locale setting and maybe even on the PostgreSQL version. The 
problem is that this locale depending output is returned already at the 
deepest level in pgmodule.c by PQgetvalue().

D'Arcy, can you comment on this? I'm not sure whether my analysis is 
correct.

-- Christoph