Re: [Pypgsql-users] Timestamp with and without time zone...

[Andrew M. <an...@ob...>]

>I believe that pyPgSQL shouldn't (have to) fuzz around with
>adjusting the date/time data in the first place.

And if that's what it did, I would be happy.

>> Operating on timezones is like waltzing through a minefield - I'm not
>> sure what to suggest to fix this, but I think the PG_TIMESTAMP type
>> ("without time zone") should make the round-trip to postgres and back
>> unmollested by default.
>That is quite true. If one retrieves a "timestamp" field
>there is no timezone information to be had as there is none.
>
>Maybe I am missing the real issue ?

I think so. mx.DateTime objects are just a date and time. While they have
an idea of the current timezone offset, they're neither UTC nor localtime.
They provide two convenience methods, localtime() and gmtime() which
add and subtract the gmtoffset() respectively. The user of the type can
choose whether the source DateTime object represents localtime or gmtime.

Now, the PG_TIMESTAMPTZ string includes a timezone offset in it's value,
and Parser.DateTimeFromString correctly honours this, so it's a mistake to 
then apply the system timezone adjustment to the value.

In the PG_TIMESTAMP case, pgPgSQL cannot know whether the value returned
from the user's database is localtime() or gmtime() *BUT* an insert/update
and subsequent fetch should be idempotent - it should return the same
value inserted - which doesn't happen if you adjust by gmtoffset().

The third issue is, I think, that useUTCtimeValue isn't applied
consistently - the handleArray() method doesn't honour it.

-- 
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/