[egenix-users] mxDateTime on pyPgSQL/PostgreSQL applies timezone?

[Kingsley Turner]

On 13/01/11 20:04, M.-A. Lemburg wrote:
> I'm confused as to what's going on with a PostgreSQL "timestamp" type
>> converted back to string.
>>
>> Selecting my timestamp field from PostgreSQL via psql gives:
>>
>>     2011-01-12 21:54:11
>>
>>
>> Yet in my code, when I str() or mx.DateTime.ISO.strUTC() the mx.DateTime
>> representation of the field returned by pyPgSQL, I get
>>
>>     2011-01-13 08:01:04.00
>>     2011-01-13 08:01:04+0000
>>
>>
>> I'm currently in UTC+11 (10+summer)
>>
>> Any suggestions as to what might be happening here?
>>
>> When I first connect to PostgreSQL, I always SET TIMEZONE UTC.
> mxDateTime stores the date/time value depending on what you
> pass to the constructor. It does not manipulate the value,
> but does apply timezone conversions in case you tell it to
> convert the time to UTC.
>
> You would have to track down how PyPgSQL creates the mxDateTime
> instances to tell whether the wrong constructor is being used
> or whether PyPgSQL itself converts the stored UTC time value
> to local time.
>
> I am a bit confused about the two examples you gave, since
> they should not differ in the minute and seconds values.
> The first appears to point to a different database row.

Thanks for that.

I admit my example is not very good, the two times should be the same as far 
as minutes go, but with the hours incorrect.  I've corrected it above ;)  
The big problem was that it was ~ 10 hours out.

I'm in the process of trying another PostgreSQL module, both pyGreSQL or 
pyscopg so far.

cheers,
-Kingsley