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.
>
> 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
Somewhat, yes; chiefly ticket #2626, though (which I originally
reported, and now updated to reflect the proposal above).
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.
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.
All PostgreSQL TIMESTAMPs are stored internally as UTC.
Since Django does everything with naive datetimes (a *huge* mistake
IMHO --),
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.