Proposal: PostgreSQL backends should *stop* using settings.TIME_ZONE
Tom Tobin
"DateTimeZoneField" that takes a "time_zone" argument; time_zone can
be a tzinfo instance or a field lookup to a zoneinfo string (e.g.,
"America/Chicago"). The field can then consume any sort of datetime
object, and will always spit back a timezone-aware datetime object
(i.e., one with a tzinfo instance attached). This works wonderfully
*except* for the PostgreSQL backends; since those backends actually
use settings.TIME_ZONE to munge the data coming in and out of the
database, things get pathological fast when changing
settings.TIME_ZONE or database backends.
I propose that the PostgresSQL backends start acting like the other
backends regarding time zones: they should start ignoring them
completely (i.e., use a plain TIMESTAMP [WITHOUT TIME ZONE], and stop
using SET TIME ZONE on connections), and leave time zone handling to
appropriate Fields. All PostgresSQL supports in terms of *storage* is
a fixed time zone offset, not the actual zoneinfo name; this isn't
very useful, and causes problems such as the above.
Ramiro Morales
>
> I propose that the PostgresSQL backends start acting like the other
> backends regarding time zones: they should start ignoring them
> completely (i.e., use a plain TIMESTAMP [WITHOUT TIME ZONE], and stop
> using SET TIME ZONE on connections), and leave time zone handling to
> appropriate Fields. All PostgresSQL supports in terms of *storage* is
> a fixed time zone offset, not the actual zoneinfo name; this isn't
> very useful, and causes problems such as the above.
>
Is this related to ticket [1]1480?.
--
Ramiro Morales
Tom Tobin
Somewhat, yes; chiefly ticket #2626, though (which I originally
reported, and now updated to reflect the proposal above).
Antti Haapala
On 25 kesä, 19:12, "Tom Tobin" <korp...@korpios.com> wrote:
> All PostgresSQL supports in terms of *storage* is
TIME ZONE! I found it out the hard way yesterday.
BTW, what we would need at the moment in our project would be a
DateTimeField with complete time zone support. It seems I have to both
write the Field type completely from scratch, and it seems it has to
be multi-column on MySQL (must store time in UTC, along with separate
textual time zone). Anyway, I believe that that approach together with
time zone aware datetime objects would get rid of lot of ambiguity and
problems for good.
To someone thinking about the problems involved with storing times in
local time: The reason why this is bad is the DST changes: on the last
Sunday of October in EU there's an extra hour added, with for example
times 3:00-3:59 occuring twice in EE(S)T zone. If you don't store the
time zone, hours 1:00-3:00 UTC will be ambiguous. Also, were you
storing local times in database, you could pretty easily ruin the
database if you decided to change the time zone in the model...
Tom Tobin
>
>
> On 25 kesä, 19:12, "Tom Tobin" <korp...@korpios.com> wrote:
>
>> All PostgresSQL supports in terms of *storage* is
>> a fixedtimezoneoffset, not the actual zoneinfo name; this isn't
>> very useful, and causes problems such as the above.
>
> Actually, in PostgreSQL TIMESTAMP WITH TIME ZONE does NOT store the
> TIME ZONE! I found it out the hard way yesterday.
Well, it stores the *offset*, which is virtually useless in real-world
applications. All PostgreSQL TIMESTAMPs are stored internally as UTC.
Since Django does everything with naive datetimes (a *huge* mistake
IMHO, and what I'm trying to correct), the Postgres backend stores
these in the database as per settings.TIME_ZONE, with that offset. It
also retrieves as per that offset, back into naive datetimes. If you
change settings.TIME_ZONE, you now get different naive datetimes back
with no way to distinguish them. If you want to use multiple time
zones at the same time ... good luck, you'll need it. ^_^
Check out the code in Bulbs (The Onion's open source code snippets)
for my current crack at this problem; we're about to push a major site
live with my "DateTimeZoneField", and I'd love to see something
similar make its way into Django:
It could use better documentation, which is on my to-do list as soon
as the APIs stabilize a tad.
Antti Haapala
On Fri, Jun 27, 2008 at 5:55 AM, Antti Haapala <an...@redinnovation.com> wrote:Well, it stores the *offset*, which is virtually useless in real-world
>
>
> On 25 kesä, 19:12, "Tom Tobin" <korp...@korpios.com> wrote:
applications.
I haven't tested on 8.3, but on 8.2 it does not. In PostgreSQL, the datatype "timestamp" corresponds to python naive datetimes.
"timestamp with time zone" instead means: "convert the given input from to UTC; default to client timezone if none specified explicitly.
When outputting, convert to the client's local time zone."
In addition, the time zones are returned in offset format because SQL99 mandates that :/.
But PostgreSQL never really stores zones, which AFAIK is a violation of the SQL99 optional feature that specifies timestamp with tz.
All PostgreSQL TIMESTAMPs are stored internally as UTC.
Only "timestamp with time zone"; "timestamps without time zone" should be considered naive...
Since Django does everything with naive datetimes (a *huge* mistake
IMHO --),
I second that. However, python time zone aware datetimes are not without problems either, and those should be considered in Django too.
However, I have yet to publish the blog entry that I've written about it.
--
Antti Haapala
Associate, Red Innovation Ltd.
+358 50 369 3535
www.redinnovation.com
Tom Tobin
>
> 2008/6/27 Tom Tobin <kor...@korpios.com>:
>>
>> Well, it stores the *offset*, which is virtually useless in real-world
>> applications.
>
> I haven't tested on 8.3, but on 8.2 it does not. In PostgreSQL, the datatype
> "timestamp" corresponds to python naive datetimes.
> "timestamp with time zone" instead means: "convert the given input from to
> UTC; default to client timezone if none specified explicitly.
> When outputting, convert to the client's local time zone."
Django uses "TIMESTAMP WITH TIME ZONE" in the "creation" module for
the PostgreSQL backends. But yes, PostgreSQL behaves as you describe.
> In addition, the time zones are returned in offset format because SQL99
> mandates that :/.
If we don't use "WITH TIME ZONE", the offset isn't part of the returned format.
> But PostgreSQL never really stores zones, which AFAIK is a violation of the
> SQL99 optional feature that specifies timestamp with tz.
AFAIK SQL99 doesn't specify storing full zoneinfo-style time zones;
PostgreSQL can store offsets, which (as outlined) are fairly useless.
>> Since Django does everything with naive datetimes (a *huge* mistake
>> IMHO --),
>
> I second that. However, python time zone aware datetimes are not without
> problems either, and those should be considered in Django too.
> However, I have yet to publish the blog entry that I've written about it.
Well the problem with Python's time zone support is that *it's not
there*; the datetime object can take a "tzinfo" argument which should
be a tzinfo object ... and then they leave it up to you to implement
anything useful. These batteries certainly *weren't* included, alas.
Thankfully, both dateutil and pytz have support for zoneinfo-style
(i.e., proper) time zones; my code uses dateutil, but I'm considering
adding support for pytz as well.
If you have a blog entry's worth of thoughts on this, I'd love to see
it. ^_^ Once we've been running our time zone code for a few weeks
(and I've made the corresponding tweaks), I'd like to write up a full
article on the topic.
Santiago Aguiar
PITA, but I still love it :).
I removed the 'with timezone' from django postgresql creation module,
and store all my datetimes as naive, knowing that internally I only
use UTC.
Then, with pytz + babel support, I used some filters inspired on
babeldjango filters (http://svn.edgewall.org/repos/babel/contrib/
django/babeldjango/templatetags/babel.py) that get the timezone
configured for the current user (seted on a thread local thru a
middleware based on a cookie) to display the datetimes in the user
selected timezone.
IMHO, something like this is the way to go, I don't know why would you
want to store the timezone with the datetime, *except* you wanted to
display times in different timezones for the same user, which I think
is quite and edge case. Maybe in that case, you should add an extra
column for the timezone, but I wouldn't do it for all datetimes.