On Jun 29, 12:01 pm, Ryan McIntosh <thebigsl...@gmail.com> wrote:
> Is that a crutch of your database? It isn't something I would ever live
> with. All the *sql database servers I hate have an idea of what a
> timestamp+offset is. Django should leverage this. IMHO, no such field
> should exist in the underlying database but for ticks since epoch if tzs
> aren't supported... Everything useful should be derived if necessar...
I think there is a misunderstanding of UTC and timezones in general in
this thread. If TZ-aware datetime handling were to be added to Django,
it would almost certainly require the pytz module. That module
contains sufficient information to convert any UTC timestamp to any TZ
in the world, dating back sometimes as far as a couple of hundred
years, and reaching forward in the future (pending any future changes
of DST rules for a particular timezone).
It is redundant to store the original TZ in a clip-on field, if the
original input is properly handled. Take for example the following
three timestamps:
'1969-07-20T20:17:40+00:00'
'1969-07-20T16:17:40-04:00'
'1969-07-20T21:17:40+01:00'
They are all the same moment in time. The fact that they are expressed
as different local times is only relevant to the observer. Which one
will a (half-decent) DB engine store? The UTC one. Which one do I want
to show my users? Well, that depends which timezone they live in...
NOT which timezone the data was input.
PS: The timestamp example I've used, incidentally, is the Apollo 11
moon landing. Which timezone was it on the moon? UTC, like most things
in astronomy. Which timezone would TV news channels have reported?
Probably whichever TZ that news channel was broadcast in... all around
the world.
PPS: The moon landing occurred a few months too early to be able to be
stored in a Unix epoch timestamp. That rules out MySQL's "timestamp"
field....
With pytz, I can convert any non-naive timestamp to any other TZ that
I need to. And if I have users in multiple timezones, then I most
likely will want to render timestamps in multiple timezones. The only
situation I can think of where timestamps are nearly always expressed
in the local TZ are flight departure/arrivals. And since you would
most likely also be storing the departure/arrival *airport*, you can
deduce from there what TZ the timestamp should be displayed as, in
order to be a "local time".
On Jun 29, 3:59 pm, Sam Bull <osir...@gmail.com> wrote:
> I think we agree here. I'm suggesting the separate tz field as a
> nice-to-have. I think Django has everything it needs to make DateTimeField
> timezone-aware. It sounds like its already doing the right thing with
> postgresql. It just needs a graceful fallback for other DBs that don't do
> timezones as well.
Django simply sets the client connection TZ (from settings.py) when
connecting to Postgres. From that point on, Postgres will assume that
all naive timestamps Django sends it (which at the moment, is all of
them...) should be interpreted as being in that TZ. Postgres still
stores "timestamp with time zone" fields internally as UTC Julian
dates however. It will also by default render them in a SELECT as the
client connection's timezone. You can however explicitly specific the
TZ when doing an INSERT, eg
insert into foo values ('1999-01-08 04:05:06 -8:00');
You can also convert timestamps explicitly to other timezones in the
Postgres backend itself, using the "AS TIME ZONE" operator. It should
be sufficient however for Postgres to simply return the timestamp+TZ
(*any* TZ), for Python to construct a non-naive datetime with tzinfo
struct.
Believe it or not, MySQL also does things much the same way as
Postgres, when using the MySQL "timestamp" field type. MySQL has a
client connection TZ, and will assume any input timestamps that are
not qualified with a TZ to be in that client connection's TZ. It also
implicitly converts the stored UTC timestamp values to the client
connection's TZ. I gave a clear example of that happening in an
earlier post. BUT.... Django does not use MySQL's "timestamp" field
type for storing datetime objects - it instead uses the MySQL
"datetime" field type, which does NOT support any concept of TZ...
that is, they are stored as naive datetimes.
The main difference between MySQL's "timestamp" field type and
PostgreSQL's "timestamp with time zone" field type (and indeed also
MySQL's "datetime" field type) is the date range that they support.
MySQL's "timestamp" field type is modeled on Unix epoch timestamps,
and therefore does not support dates earlier than 1 Jan, 1970 00:00:00
UTC. PostgreSQL's "timestamp with time zone" field type on the other
hand is modeled on Julian dates, and supports dates ranging from 4713
BC to 294276 AD (with 1 µs accuracy, I might add). And MySQL's
"datetime" field type supports some bizarre range of
'0000-00-00' (yes, you really can specify the zero'th day of the
zero'th month... isn't that cool?) to '9999-12-31'. One can only hope
that Oracle's acquisition of MySQL might one day lead to better SQL
compliance.
I think the real issue here is that Django should really start to
think pretty hard about using non-naive datetimes, everywhere. We
already have pretty good l10n and i18n support in Django. So if we
acknowledge the fact that we have users with different locales/
languages, why do ignore the fact that they're often in different
timezones too?
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en.
