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

On Tue, May 24, 2005 at 09:55:37AM +1000, Andrew McNamara wrote:

> 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.
Well, they are neither themselves. However, if they know how to
get from their value to UTC (this is what "having an idea of
the current timezone offset" implies) they can be considered
localtime, right ?

And if that is so one would want to make sure that queries
return local time such that the local time value ends up in
the mx.DateTime object - which "has an idea" of how to convert
that to UTC.

So, users of pyPgSQL would be well advised to make sure they
SELECT timestamps such that they'll get back the local time
zone. Two proper ways do so come to mind:

tz_offset = get_tz_offset() # from wherever, say, user or locale
and then:
cursor.execute('set client_encoding to %s', tz_offset)
or
cursor.execute('select tz_col at time zone %s from ... where ...' % tz_offset, args)

Using that approach one should be on the safe side. In fact,
in GNUmed we are using that approach and haven't yet had any
trouble.

> In the PG_TIMESTAMP case, pyPgSQL 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().
This ain't quite as easy as it sounds. Given the programmer
did everything correctly she will retrieve the same *point* in
time as she stored. However, that point in time may well be
expressed differently from the insert statement, namely when
insert and select timezones are different.

IOW, there is NO WAY even inside PostgreSQL to find out the
*representation* of the point in time at the INSERT location
without additional data - because PG does not store a "source"
timezone. We work around that issue - should the need arise to
do so - by explicitely storing a "source" timezone nearby the
value (not perfect but close). Here's a scenario:

Imagine you are looking at a Cortisol level reading of a
patient. You think to yourself: "Now, this level is rather
high, this patient better undergo imaging for kidney/lung cancer!"
However, that patient's reading isn't really high at all - it
simply so happens that the reading was taken at 7 in the
morning in Bangkok while it is presented to you as taken at
midnight (Bangkok time converted to my local time zone here in
Germany!). The reason is that Cortisol levels vary greatly
throughout the day...

Of course that's a severe frontend error but one needs to be
aware of such issues. And if the INSERT timezone wasn't stored
in the first place there would be no way to find out ...

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