Hello, This week, I've gathered all the information I need about how the database engines and adapters supported by Django handle datetime objects. I'm attaching my findings.
The good news is that the database representations currently used by Django are already optimal for my proposal. I'll store data in UTC: - with an explicit timezone on PostgreSQL, - without timezone on SQLite and MySQL because the database engine doesn't support it, - without timezone on Oracle because the database adapter doesn't support it. Currently, Django sets the "supports_timezones feature" to True for SQLite. I'm skeptical about this choice. Indeed, the time zone is stored: SQLite just saves the output of "<datetime>.isoformat(), which includes the UTC offset for aware datetime objects. However, the timezone information is ignored when reading the data back from the database, thus yielding incorrect data when it's different from the local time defined by settings.TIME_ZONE. As far as I can tell, the "supports_timezones" and the "needs_datetime_string_cast" database features are incompatible, at least with the current implementation of "typecast_timestamp". There's a comment about this problem that dates back to the merge of magic-removal, possibly before: https://code.djangoproject.com/browser/django/trunk/django/db/backends/util.py?annotate=blame#L79 SQLite is the only engine who has these two flags set to True. I think "supports_timezones" should be False. Does anyone know why it's True? Is it just an historical artifact? Finally, I have read the document that describes "to_python", "value_to_string", and r"get_(db_)?prep_(value|save|lookup)". The next step is to adjust these functions in DateFieldField, depending on the value of settings.USE_TZ. Best regards, -- Aymeric Augustin.
On 11 sept. 2011, at 23:18, Aymeric Augustin wrote: > Hello, > > Given the positive feedback received here and on IRC, I've started the > implementation. > > Being most familiar with mercurial, I've forked the Bitbucket mirror. This > page that compares my branch to trunk: > https://bitbucket.org/aaugustin/django/compare/..django/django > > I've read a lot of code in django.db, and also the documentation of > PostgreSQL, MySQL and SQLite regarding date/time types. > > I've written some tests that validate the current behavior of Django. Their > goal is to guarantee backwards-compatibility when USE_TZ = False. > > At first they failed because runtests.py doesn't set os.environ['TZ'] and > doesn't call time.tzset(), so the tests ran with my system local time. I > fixed that in setUp and tearDown. Maybe we should call them in runtests.py > too for consistency? > > By the way, since everything is supposed to be in UTC internally when USE_TZ > is True, it is theoretically to get rid of os.environ['TZ'] and time.tzset(). > They are only useful to make timezone-dependant functions respect the > TIME_ZONE setting. However, for backwards compatibility (in particular with > third-party apps), it's better to keep them and interpret naive datetimes in > the timezone defined by settings.TIME_ZONE (instead of rejecting them > outright). For this reason, I've decided to keep os.environ['TZ'] and > time.tzset() even when USE_TZ is True. > > Best regards, > > -- > Aymeric Augustin. > > > On 3 sept. 2011, at 17:40, Aymeric Augustin wrote: > >> Hello, >> >> The GSoC proposal "Multiple timezone support for datetime representation" >> wasn't picked up in 2011 and 2010. Although I'm not a student and the summer >> is over, I'd like to tackle this problem, and I would appreciate it very >> much if a core developer accepted to mentor me during this work, GSoC-style. >> >> Here is my proposal, following the GSoC guidelines. I apologize for the wall >> of text; this has been discussed many times in the past 4 years and I've >> tried to address as many concerns and objections as possible. >> >> Definition of success >> --------------------- >> >> The goal is to resolve ticket #2626 in Django 1.4 or 1.5 (depending on when >> 1.4 is released). >> >> Design specification >> -------------------- >> >> Some background on timezones in Django and Python >> ................................................. >> >> Currently, Django stores datetime objects in local time in the database, >> local time being defined by the TIME_ZONE setting. It retrieves them as >> naive datetime objects. As a consequence, developers work with naive >> datetime objects in local time. >> >> This approach sort of works when all the users are in the same timezone and >> don't care about data loss (inconsistencies) when DST kicks in or out. >> Unfortunately, these assumptions aren't true for many Django projects: for >> instance, one may want to log sessions (login/logout) for security purposes: >> that's a 24/7 flow of important data. Read tickets #2626 and #10587 for more >> details. >> >> Python's standard library provides limited support for timezones, but this >> gap is filled by pytz <http://pytz.sourceforge.net/>. If you aren't familiar >> with the topic, strongly recommend reading this page before my proposal. It >> explains the problems of working in local time and the limitations of >> Python's APIs. It has a lot of examples, too. >> >> Django should use timezone-aware UTC datetimes internally >> ......................................................... >> >> Example : datetime.datetime(2011, 09, 23, 8, 34, 12, tzinfo=pytz.utc) >> >> In my opinion, the problem of local time is strikingly similar to the >> problem character encodings. Django uses only unicode internally and >> converts at the borders (HTTP requests/responses and database). I propose a >> similar solution: Django should always use UTC internally, and conversion >> should happen at the borders, i.e. when rendering the templates and >> processing POST data (in form fields/widgets). I'll discuss the database in >> the next section. >> >> Quoting pytz' docs: "The preferred way of dealing with times is to always >> work in UTC, converting to localtime only when generating output to be read >> by humans." I think we can trust pytz' developers on this topic. >> >> Note that a timezone-aware UTC datetime is different from a naive datetime. >> If we were using naive datetimes, and assuming we're using pytz, a developer >> could write: >> >> mytimezone.localize(datetime_django_gave_me) >> >> which is incorrect, because it will interpret the naive datetime as local >> time in "mytimezone". With timezone-aware UTC datetime, this kind of errors >> can't happen, and the equivalent code is: >> >> datetime_django_gave_me.astimezone(mytimezone) >> >> Django should store datetimes in UTC in the database >> .................................................... >> >> This horse has been beaten to death on this mailing-list so many times that >> I'll keep the argumentation short. If Django handles everything as UTC >> internally, it isn't useful to convert to anything else for storage, and >> re-convert to UTC at retrieval. >> >> In order to make the database portable and interoperable: >> - in databases that support timezones (at least PostgreSQL), the timezone >> should be set to UTC, so that the data is unambiguous; >> - in databases that don't (at least SQLite), storing data in UTC is the >> most reasonable choice: if there's a "default timezone", that's UTC. >> >> I don't intend to change the storage format of datetimes. It has been >> proposed on this mailing-list to store datetimes with original timezone >> information. However, I suspect that in many cases, datetimes don't have a >> significant "original timezone" by themselves. Furthermore, there are many >> different ways to implemented this outside of Django's core. One is to store >> a local date + a local time + a place or timezone + is_dst flag and skip >> datetime entirely. Another is to store an UTC datetime + a place or >> timezone. In the end, since there's no obvious and consensual way to >> implement this idea, I've chosen to exclude it from my proposal. See the >> "Timezone-aware storage of DateTime" thread on this mailing list for a long >> and non-conclusive discussion of this idea. >> >> I'm expecting to take some flak because of this choice :) Indeed, if you're >> writing a multi-timezone calendaring application, my work isn't going to >> resolve all your problems — but it won't hurt either. It may even provide a >> saner foundation to build upon. Once again, there's more than one way to >> solve this problem, and I'm afraid that choosing one would offend some >> people sufficiently to get the entire proposal rejected. >> >> Django should convert between UTC and local time in the templates and forms >> ........................................................................... >> >> I regard the problem of local time (in which time zone is my user?) as very >> similar to internationalization (which language does my user read?), and >> even more to localization (in which country does my user live?), because >> localization happens both on output and on input. >> >> I want controllable conversion to local time when rendering a datetime in a >> template. I will introduce: >> - a template tag, {% localtime on|off %}, that works exactly like {% >> localize on|off %}; it will be available with {% load tz %}; >> - two template filters, {{ datetime|localtime }} and {{ datetime|utctime >> }}, that work exactly like {{ value|localize }} and {{ value|unlocalize }}. >> >> I will convert datetimes to local time when rendering a DateTimeInput >> widget, and also handle SplitDateTimeWidget and SplitHiddenDateTimeWidget >> which are more complicated. >> >> Finally, I will convert datetimes entered by end-users in forms to UTC. I >> can't think of cases where you'd want an interface in local time but user >> input in UTC. As a consequence, I don't plan to introduce the equivalent of >> the `localize` keyword argument in form fields, unless someone brings up a >> sufficiently general use case. >> >> How to set each user's timezone >> ............................... >> >> Internationalization and localization are based on the LANGUAGES setting. >> There's a widely accepted standard to select automatically the proper >> language and country, the Accept-Language header. >> >> Unfortunately, some countries like the USA have more than one timezone, so >> country information isn't enough to select a timezone. To the best of my >> knowledge, there isn't a widely accepted way to determine the timezones of >> the end users on the web. >> >> I intend to use the TIME_ZONE setting by default and to provide an >> equivalent of `translation.activate()` for setting the timezone. With this >> feature, developers can implement their own middleware to set the timezone >> for each user, for instance they may want to use >> <http://pytz.sourceforge.net/#country-information>. >> >> This means I'll have to introduce another thread local. I know this is >> frowned upon. I'd be very interested if someone has a better idea. >> >> It might be no longer necessary to set os.environ['TZ'] and run time.tzset() >> at all. That would avoid a number of problems and make Windows as well >> supported as Unix-based OSes — there's a bunch of tickets in Trac about this. >> >> I'm less familiar with this part of the project and I'm interested in advice >> about how to implement it properly. >> >> Backwards compatibility >> ....................... >> >> Most previous attempts to resolve have stumbled upon this problem. >> >> I propose to introduce a USE_TZ settings (yes, I know, yet another setting) >> that works exactly like USE_L10N. If set to False, the default, you will get >> the legacy (current) behavior. Thus, existing websites won't be affected. If >> set to True, you will get the new behavior described above. >> >> I will also explain in the release notes how to migrate a database — which >> means shifting all datetimes to UTC. I will attempt to develop a script to >> automate this task. >> >> Dependency on pytz >> .................. >> >> I plan to make pytz a mandatory dependency when USE_TZ is True. This would >> be similar to the dependency on on gettext when USE_I18N is True. >> >> pytz gets a new release every time the Olson database is updated. For this >> reason, it's better not to copy it in Django, unlike simplejson and >> unittest2. >> >> It was split from Zope some time ago. It's a small amount of clean code and >> it could be maintained within Django if it was abandoned (however unlikely >> that sounds). >> >> Miscellaneous >> ............. >> >> The following items have caused bugs in the past and should be checked >> carefully: >> >> - caching: add timezone to cache key? See #5691. >> - functions that use LocalTimezone: naturaltime, timesince, timeuntil, >> dateformat. >> - os.environ['TZ']. See #14264. >> - time.tzset() isn't supported on Windows. See #7062. >> >> Finally, my proposal shares some ideas with >> https://github.com/brosner/django-timezones; I didn't find any >> documentation, but I intend to review the code. >> >> About me >> -------- >> >> I've been working with Django since 2008. I'm doing a lot of triage in Trac, >> I've written some patches (notably r16349, r16539, r16548, also some >> documentation improvements and bug fixes), and I've helped to set up >> continuous integration (especially for Oracle). In my day job, I'm producing >> enterprise software based on Django with a team of ten developers. >> >> Work plan >> --------- >> >> Besides the research that's about 50% done, and discussion that's going to >> take place now, I expect the implementation and tests to take me around 80h. >> Given how much free time I can devote to Django, this means three to six >> months. >> >> Here's an overview of my work plan: >> >> - Implement the USE_TZ flag and database support — this requires checking >> the capabilities of each supported database in terms of datetime types and >> time zone support. Write tests, especially to ensure backwards >> compatibility. Write docs. (20h) >> >> - Implement timezone localization in templates. Write tests. Write docs. >> (10h) >> >> - Implement timezone localization in widgets and forms. Check the admin >> thoroughly. Write tests. Write docs. (15h) >> >> - Implement the utilities to set the user's timezone. Write tests. Write >> docs. (15h) >> >> - Reviews, etc. (20h) >> >> What's next? >> ------------ >> >> Constructive criticism, obviously :) Remember that the main problems here >> are backwards-compatibility and keeping things simple. >> >> Best regards, >> >> -- >> Aymeric. >> >> >> >> >> >> Annex: Research notes >> --------------------- >> >> Wiki >> .... >> >> [GSOC] >> https://code.djangoproject.com/wiki/SummerOfCode2011#Multipletimezonesupportfordatetimerepresentation >> >> Relevant tickets >> ................ >> >> #2626: canonical ticket for this issue >> >> #2447: dupe, an alternative solution >> #8953: dupe, not much info >> #10587: dupe, a fairly complete proposal, but doesn't address backwards >> compatibility for existing data >> >> Relevant related tickets >> ........................ >> >> #14253: how should "now" behave in the admin when "client time" != "server >> time"? >> >> Irrelevant related tickets >> .......................... >> >> #11385: make it possible to enter data in a different timezone in >> DateTimeField >> #12666: timezone in the 'Date:' headers of outgoing emails - independant >> resolution >> >> Relevant threads >> ................ >> >> 2011-05-31 Timezone-aware storage of DateTime >> http://groups.google.com/group/django-developers/browse_thread/thread/76e2b486d561ab79 >> >> 2010-08-16 Datetimes with timezones for mysql >> https://groups.google.com/group/django-developers/browse_thread/thread/5e220687b7af26f5 >> >> 2009-03-23 Django internal datetime handling >> https://groups.google.com/group/django-developers/browse_thread/thread/ca023360ab457b91 >> >> 2008-06-25 Proposal: PostgreSQL backends should *stop* using >> settings.TIME_ZONE >> http://groups.google.com/group/django-developers/browse_thread/thread/b8c885389374c040 >> >> 2007-12-02 Timezone aware datetimes and MySQL (ticket #5304) >> https://groups.google.com/group/django-developers/browse_thread/thread/a9d765f83f552fa4 >> >> Relevant related threads >> ........................ >> >> 2009-11-24 Why not datetime.utcnow() in auto_now/auto_now_add >> http://groups.google.com/group/django-developers/browse_thread/thread/4ca560ef33c88bf3 >> >> Irrelevant related threads >> .......................... >> >> 2011-07-25 "c" date formating and Internet usage >> https://groups.google.com/group/django-developers/browse_thread/thread/61296125a4774291 >> >> 2011-02-10 GSoC 2011 student contribution >> https://groups.google.com/group/django-developers/browse_thread/thread/0596b562cdaeac97/585ce1b04632198a?#585ce1b04632198a >> >> 2010-11-04 Changing settings per test >> https://groups.google.com/group/django-developers/browse_thread/thread/65aabb45687e572e >> >> 2009-09-15 What is the status of auto_now and auto_now_add? >> https://groups.google.com/group/django-developers/browse_thread/thread/cd1a76bca6055179 >> >> 2009-03-09 TimeField broken in Oracle >> https://groups.google.com/group/django-developers/browse_thread/thread/bba2f80a2ca9b068 >> >> 2009-01-12 Rolling back tests -- status and open issues >> https://groups.google.com/group/django-developers/browse_thread/thread/1e4f4c840b180895 >> >> 2008-08-05 Transactional testsuite >> https://groups.google.com/group/django-developers/browse_thread/thread/49aa551ad41fb919 >> >Title: Database representation of date and time
Database representation of date and time
SQLite
Documentation
Data types
SQLite has five storage classes: NULL, INTEGER, REAL, TEXT, BLOB.
SQLite can store dates and times as TEXT (ISO8601), REAL (Julian days) or INTEGER (Unix Time). It provides functions capable of operating over these representations: date, time, datetime, julianday, strftime.
These functions can convert between UTC and local time but don't handle timezones otherwise. This capability is based on C library functions, so it's roughly equivalent to Python's time module.
Behavior
Python's sqlite3 module supports adapters (Python object => SQLite) and converters (SQLite => Python object). It has default adapters for Python's built-in datetime.date and datetime.datetime types, and corresponding converters (registered under the names date and timestamp).
The datetime adapter uses datetime.isoformat(' '). If the datetime is aware, this will append the UTC offset (+HH:MM) to the representation. But this isn't supported by SQLite's functions and it loses information: a timezone (tzinfo object) contains more information than an UTC offset.
This implementation is reasonnable if one just wants to print a correct date without a costly conversion. But in our case, datetimes are alread converted to UTC, so they should just be stored without offset indication.
Django overrides the default converters. The converter to datetime.datetime objects, typecast_timestamp, is capable of parsing the time zone but it currently throws it away! Arguably, this is a bug: if a datetime is aware and has a timezone that isn't the local time, a round trip in the database changes the value. When an input contains timezone information, either this information must be taken into account, or the input must be rejected. Corrupting data silently isn't acceptable.
To sum up, SQLite just stores the string that it receives and sends it back. For portability and consistency, it's best to restrict ourselves to the formats accepted by its date and time handling functions.
Conclusion
Use YYYY-MM-DD HH:MM:SS in UTC for read and write. Reject everything else.
PostgreSQL
Documentation
Data types
TIMESTAMP = Julian time in microseconds
- equivalent to a Python datetime.datetime without tzinfo (naive).
TIMESTAMP WITH TIME ZONE = Julian time in microseconds
- equivalent to a Python datetime.datetime without tzinfo (naive) too.
- stored in UTC converted to the local time of the current connection
DATE = Julian day number
- equivalent to a Python datetime.date
TIME = time of day in microseconds
- equivalent to a naive Python datetime.time
TIME WITH TIME ZONE = time of day in microseconds + offset in minutes
- equivalent to an aware Python datetime.time — which is pathological
- "the definition exhibits properties which lead to questionable usefulness"
INTERVAL
- equivalent to a Python datetime.timedelta
- not relevant for this discussion
Behavior
PostgreSQL will interpret almost any reasonnably formatted date or time.
On input, postgresql_psycopg2 converts a Python object to <value>'::<type>, where:
- <value> is obtained by calling isoformat() on the object,
- <type> is one of timestamptz, timestamp, date, time, depending on the type of the object and whether its tzinfo is None.
PostgreSQL discards silently the timezone of TIMESTAMP literals, which inserts a wrong value. It interprets the timezone of TIMESTAMP WITH TIME ZONE literals and uses it to convert the value to UTC, then it stores the result. If a TIMESTAMP WITH TIME ZONE does not contain a time zone, it is assumed to be in the local time zone.
Internally, PostgreSQL also assumes that TIMESTAMP [WITHOUT TIME ZONE] is in local time, for instance comparing one to a TIMESTAMP WITH TIME ZONE. So it's a bad idea to store UTC data in a TIMESTAMP column.
On output, postgresql_psycopg2 creates appropriate datetime, date, time and timedelta objects. The tzinfo is created by cursor.tzinfo_factory. By default, this is set to psycopg2.tz.FixedOffsetTimezone, but Django changes it to None. As a consequence, Django always obtains naive datetimes.
Conclusion
Set the connection's time zone to UTC. Use TIMEZONE WITH TIMESTAMP. Send data in UTC without tzinfo and set tzinfo_factory to None to avoid unecessary processing.
MySQL
Documentation
Data types
DATETIME = YYYY-MM-DD HH:MM:SS, 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
- equivalent to a Python datetime.datetime without microseconds and without tzinfo (naive).
DATE = YYYY-MM-DD, 1000-01-01 to 9999-12-31
- equivalent to a Python datetime.date.
TIMESTAMP = Unix timestamp, 1970-01-01 00:00:01 to 2038-01-19 03:14:07
- equivalent to a Python time.time with a 1 second resolution.
- supports auto-initialization and auto-update.
TIME = HH:MM:SS, -838:59:59 to 838:59:59
- like a Python datetime.timedelta without microseconds and with a smaller range.
- superset of a Python datetime.time without microseconds and without tzinfo (naive).
YEAR = YYYY, from 1901 to 2155.
Behavior
Microseconds are silently dicarded. Timezone information is discarded with a warning, which inserts a wrong value.
TIMESTAMP always converts from local time to UTC for storage and from UTC to local time for retrieval. This almost never makes sense in non-trivial applications.
mysql> CREATE TABLE test (dt DATETIME);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test VALUES ('2011-09-01 10:20:30.405060');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test VALUES ('2011-09-01 12:20:30+02:00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'dt' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test;
+---------------------+
| dt |
+---------------------+
| 2011-09-01 10:20:30 |
| 2011-09-01 12:20:30 |
+---------------------+
2 rows in set (0.00 sec)
mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test (ts TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
mysql> SET TIME_ZONE = '+00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test VALUES ('2011-09-01 10:20:30');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test;
+---------------------+
| ts |
+---------------------+
| 2011-09-01 10:20:30 |
+---------------------+
1 row in set (0.00 sec)
mysql> SET TIME_ZONE = 'SYSTEM';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test VALUES ('2011-09-01 10:20:30');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test;
+---------------------+
| ts |
+---------------------+
| 2011-09-01 12:20:30 |
| 2011-09-01 10:20:30 |
+---------------------+
2 rows in set (0.00 sec)
mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)
MySQLdb registers datetime_to_sql as encoder and datetime_or_None as decoder for datetimes. These functions basically support the YYYY-MM-DD HH:MM:SS format.
Conclusion
Use DATETIME. Send data in UTC without tzinfo.
Oracle
Data types
DATE = Julian time in seconds
- equivalent to a Python datetime.datetime without microseconds and without tzinfo (naive).
TIMESTAMP = Julian time in nanoseconds
- equivalent to a Python datetime.datetime with nanoseconds and without tzinfo (naive).
TIMESTAMP WITH TIME ZONE = Julian time in nanoseconds + time zone
- equivalent to a Python datetime.datetime with nanoseconds
TIMESTAMP WITH LOCAL TIME ZONE = Julian time in nanoseconds
- equivalent to a Python datetime.datetime with nanoseconds and without tzinfo (naive).
- behaves like PostgreSQL's TIMESTAMP WITH TIME ZONE, except that it's stored in the database's timezone and not in UTC.
INTERVAL
- equivalent to a Python datetime.timedelta
- not relevant for this discussion
Behavior
cx_Oracle uses TimestampVar_SetValue to convert a Python datetime object to Oracle, and TimestampVar_SetValue to convert back. These functions don't appear to contain any logic to deal with timezones. I believe that cx_Oracle only handles naive datetime objects.
Conclusion
Use TIMESTAMP, in order to be able to store microseconds. Send data in UTC without tzinfo.
-- 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.
