Multiple timezone support for datetime representation

[Aymeric Augustin]

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

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Paul McMillan]

Hi Aymeric,

First, thanks for all your triage work on track, it's very much
appreciated. This is a large proposal, but I think it covers the issue
pretty completely.

I agree with your approach of using UTC internally and converting on output.

My main concern with your proposal is that it may require two
different code paths for a lot of code. I have no objection to the
dependency on pytz, but I don't want this to result in pytz-aware code
that diverges significantly from the non-pytz default path.

I would like this new code to eventually become the default setting,
but acknowledge that there doesn't seem to be a good way to do that
and maintain our current backwards compatibility (and external
dependency) policy.

This isn't my area of expertise, so I'm not a good mentor candidate,
but I'd be excited to help you with this project where I can.

I'm wholeheartedly +1 on this.

Best,
-Paul

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Anssi Kääriäinen]

On Sep 3, 9:19 pm, Paul McMillan <p...-nyDhx7m2UN7lL05M/***@public.gmane.org> wrote:
> Hi Aymeric,
>
> First, thanks for all your triage work on track, it's very much
> appreciated. This is a large proposal, but I think it covers the issue
> pretty completely.
>
> I agree with your approach of using UTC internally and converting on output.
>
> My main concern with your proposal is that it may require two
> different code paths for a lot of code. I have no objection to the
> dependency on pytz, but I don't want this to result in pytz-aware code
> that diverges significantly from the non-pytz default path.
>
> I would like this new code to eventually become the default setting,
> but acknowledge that there doesn't seem to be a good way to do that
> and maintain our current backwards compatibility (and external
> dependency) policy.
>
> This isn't my area of expertise, so I'm not a good mentor candidate,
> but I'd be excited to help you with this project where I can.
>
> I'm wholeheartedly +1 on this.

I'm +1 also.

Some notes:

- Performance: The USE_L10N setting made rendering tables consisting
of numbers much slower (see #14290 for example). It would be good if
there wasn't any major regressions due to this. I suggest making a few
testcases upfront For example one could be a fetch of 10000 objects
from the DB. Another one is to then render the 10000 objects. The last
one is quite slow already if USE_L10N is True, so there is hope this
could actually improve that case.

- Concurrency: My quick reading of the documentation of pytz
suggests that this should not be a problem. But your implementation
would be thread safe, right? The main problem here is localizing the
timestamps.

- I want to save the following information in the database: The
meeting starts at 08:00 localtime 2011-09-04. Localtime is based on
some other information, maybe the location of the meeting room. I do
not want this information to change if there is a DST change. In the
proposal you are saying that you are not going to tackle this problem.
However, it would be very valuable if there were a way to save just a
plain datetime information in the database if the user so chooses. The
user is then free to interpret if it means 08:00 in Helsinki/Europe
timezone, or something else. Storing in UTC does not allow for this
because of DST changes. I guess I am saying that having a
models.DatetimeField(plain=True) would be a good thing...

- The default for USE_TZ should be False if not specified but True
for new projects. That is, it is included in the settings template as
True but it is False in the global_settings.py.

The design looks good. I am very interested in this. One more piece in
making Django more useful in the enterprise setting. I hope you find
somebody qualified to mentor your project.

- Anssi

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Mikhail Korobov]

Great job on summarizing the issue!

I have one concern though. Can you please explain why is USE_TZ option
better than introducing e.g. UtcDateTimeField?

USE_TZ=True will break all existing code (including external apps) which
relies on django 1.3 documented DateTimeField behavior, this can be scary
and will introduce a lot of "if getattr(settings, USE_TZ, False): #..."
statements in external apps for backwards compatibility.

Good UtcDateTimeField implementation can be released as a separate package
(and then eventually included in django itself). This way existing django
projects will be able to use it without waiting for a release and backwards
compatibility won't be broken. Are there obstacles in django itself that
prevent this option?

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To view this discussion on the web visit https://groups.google.com/d/msg/django-developers/-/3sUDTbi24PgJ.
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Daniel Swarbrick]

I'm wholeheartedly on +1 on this.

@Anssi Kääriäinen, re your localtime meeting example: this type of
scenario has been discussed on this list previously, and the generally
acknowledged solution is to use separate date and time fields, neither
of which (on their own) have a concept of timezone.

@Aymeric Augustin, re storing UTC in Postgres: this is somewhat moot,
since Postgres stores timestamps internally as a UTC Julian date
regardless of which timezone the client connection uses (client TZ
offset is dynamically added/subtracted at insert/update/select-time).
In the case of MySQL and SQLite however, it is a valid point.

I can understand some people's concerns about the dependency
requirement of pytz in order to achieve this. It would have been great
to see pytz added to the standard Python libs in 3.x, since (IMHO)
it's such a fundamental requirement of any language these days. In any
case, it will be great to see Django's international date/time
handling be on a par with its L10N and I18N features.

On Sep 4, 12:48 am, Mikhail Korobov <kmik...-gM/Ye1E23mwN+***@public.gmane.org> wrote:
> Great job on summarizing the issue!
>
> I have one concern though. Can you please explain why is USE_TZ option
> better than introducing e.g. UtcDateTimeField?
>
> USE_TZ=True will break all existing code (including external apps) which
> relies on django 1.3 documented DateTimeField behavior, this can be scary
> and will introduce a lot of "if getattr(settings, USE_TZ, False): #..."
> statements in external apps for backwards compatibility.
>
> Good UtcDateTimeField implementation can be released as a separate package
> (and then eventually included in django itself). This way existing django
> projects will be able to use it without waiting for a release and backwards
> compatibility won't be broken. Are there obstacles in django itself that
> prevent this option?

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Aymeric Augustin]

Thanks Paul, Anssi, Mikhail and Daniel for reviewing the proposal!

Here are some answers to your remarks.


On 3 sept. 2011, at 20:19, Paul McMillan wrote:
> My main concern with your proposal is that it may require two
> different code paths for a lot of code.

Currently, my best guess is:
- limited changes in the models layer
- some new code in the forms and template layers (if USE_TZ: …)
- most of the duplication will be in the tests, because I have to test everything with and without USE_TZ.

Of course, I can't be sure before actually writing the code :)

> I would like this new code to eventually become the default setting,
> but acknowledge that there doesn't seem to be a good way to do that
> and maintain our current backwards compatibility (and external
> dependency) policy.

I'm going to use the same trick used by USE_L10N and explained by Anssi: set USE_TZ to False in global_settings.py and to True in the template of settings.py. This preserves backwards compatibility but the new code is the default for new projects.



On 3 sept. 2011, at 21:19, Anssi Kääriäinen wrote:

> - Performance: The USE_L10N setting made rendering tables consisting
> of numbers much slower (see #14290 for example). It would be good if
> there wasn't any major regressions due to this.

With USE_TZ, rendering a datetime will be slower because of the conversion to local time. I have no idea of the performance of pytz. I'll do some benchmarks.

> - Concurrency: My quick reading of the documentation of pytz
> suggests that this should not be a problem. But your implementation
> would be thread safe, right? The main problem here is localizing the
> timestamps.

Certainly, the implementation must be thread safe, because requests may run concurrently in different threads for users in different timezones. I'm pretty sure it will work out of the box, and it will be even more isolated if I can avoid setting os.environ['TZ'] and calling time.tzset().

> - I want to save the following information in the database: The
> meeting starts at 08:00 localtime 2011-09-04. Localtime is based on
> some other information, maybe the location of the meeting room.
> (...) it would be very valuable if there were a way to save just a
> plain datetime information in the database if the user so chooses.

In my opinion, and more importantly in PostgreSQL's implementation, a DateTime represents a "point in time", in an absolute sense — ignoring relativistic effects :)

For your example, I support Daniel's suggestion: use a DateField and a TimeField, plus a way to obtain the time zone (like a foreign key to the meeting room). That's exactly the information you said you wanted to store :)

I've hesitated a lot to add a "naive=False/True" keyword argument to DateTimeField. Right now, I'm still convinced that mixing naive and aware DateTimes in the same application a recipe for disaster and that Django shouldn't facilitate it.



On 4 sept. 2011, at 00:48, Mikhail Korobov wrote:

> I have one concern though. Can you please explain why is USE_TZ option better than introducing e.g. UtcDateTimeField?

In short, Django shouldn't provide two different ways to represent a "point in time". Quoting the Zen of Python: "There should be one-- and preferably only one --obvious way to do it."

> USE_TZ=True will break all existing code (including external apps) which relies on django 1.3 documented DateTimeField behavior, this can be scary and will introduce a lot of "if getattr(settings, USE_TZ, False): #..." statements in external apps for backwards compatibility.

This is an interesting question. For backwards compatibility, if a DateTimeField is set to a naive datetime (like datetime.datetime.now()) and USE_TZ is True, the value should be interpreted in local time and a warning should be raised.

With this rule, I believe that most code should work just fine with or without USE_TZ, thanks to duck typing. Basically, what works on a naive datetime also works on an aware datetime, including timedelta arithmetic.

Of course, applications dedicated to dealing with timezones may break. If you're using one, you should probably stick with USE_TZ = False.

> Good UtcDateTimeField implementation can be released as a separate package (and then eventually included in django itself). This way existing django projects will be able to use it without waiting for a release and backwards compatibility won't be broken. Are there obstacles in django itself that prevent this option?

In short, my proposal can't be implemented outside of core because it requires a modification of the template engine.

If it could, I'm sure someone would have done it already — ticket #2626 has been open for five years.

In fact, I tried to implement these ideas without modifying Django in my current project. The result is way too messy for publication, let alone for eventual inclusion in Django. My entry point in the template engine is django.utils.formats.localize, and I'm monkey patching it to convert datetimes to local time. This means I'm mixing localization and timezones. I have to set TIME_ZONE = 'UTC' to get UTC in the database and introduce another setting, LOCAL_TIME_ZONE, for the local time of the project. Finally my developers must never forget to use custom DateTimeFields in models and forms, as well as a custom ModelAdmin base class, or UTC data shows up in the admin. It's totally unwieldy.



On 4 sept. 2011, at 12:34, Daniel Swarbrick wrote:
> re storing UTC in Postgres: this is somewhat moot,
> since Postgres stores timestamps internally as a UTC Julian date
> regardless of which timezone the client connection uses

Under PostgreSQL, I assume I have to use a TIMESTAMP WITH TIME ZONE and set the connection's timezone to UTC if I want non-Django applications using the same database to obtain correct results, regardless of their connection's timezone. To be honest, this is pure speculation; I must check this for each database engine.


Best regards,

--
Aymeric Augustin.

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Paul McMillan]

> I'm going to use the same trick used by USE_L10N and explained by Anssi: set USE_TZ to False in global_settings.py and to True in the template of settings.py. This preserves backwards compatibility but the new code is the default for new projects.

This isn't gonna work because your new code will have a hard dependency on pytz.

-Paul

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Aymeric Augustin]

2011/9/6 Paul McMillan <paul-nyDhx7m2UN7lL05M/***@public.gmane.org>:
>> I'm going to use the same trick used by USE_L10N and explained by Anssi: set USE_TZ to False in global_settings.py and to True in the template of settings.py. This preserves backwards compatibility but the new code is the default for new projects.
>
> This isn't gonna work because your new code will have a hard dependency on pytz.

Django uses PIL for ImageField, but it isn't a hard dependency because
it's only imported within functions that actually use it. We can do
the same for pytz if it's only used in a few places.

If a module uses pytz in many functions, I suggest this pattern:

from django.conf import settings
if settings.USE_TZ:
import pytz

And it's a bug to hit code that depends on pytz when USE_TZ is False.

If we want to make some functions (like timezone conversion helpers)
available to anyone who has pytz, regardless of the value of USE_TZ,
we could use this pattern:

try:
import pytz
except ImportError:
if settings.USE_TZ:
raise

--
Aymeric Augustin.

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Daniel Swarbrick]

On Sep 6, 10:47 pm, Aymeric Augustin
<aymeric.augus...-o/5/jSaJEHk+NdeTPqioyti2O/***@public.gmane.org> wrote:

> Under PostgreSQL, I assume I have to use a TIMESTAMP WITH TIME ZONE and set the connection's timezone to UTC if I want non-Django applications using the same database to obtain correct results, regardless of their connection's timezone. To be honest, this is pure speculation; I must check this for each database engine.

Django defaults to "timestamp with time zone" for DateTimeFields on
Postgres. Django also explicitly sets the client connection timezone
to that specified in settings.py, however normal behaviour for a
Postgres client is to inherit the TZ of the host that the client is
running on. Naive timestamps (eg. without timezone) in an INSERT are
assumed to be of the client connection timezone.

SELECTs of "timestamp with time zone" output the UTC offset, for
example, immediately after connecting, and without manually specifying
the TZ, psql inherits my host's TZ (Europe/Berlin):

talk2=> select id, last_login, date_joined from auth_user where id=1;
id | last_login | date_joined
----+-------------------------------+------------------------
1 | 2011-09-05 18:43:38.050294+02 | 2008-05-03 02:29:14+02

I can change to another TZ easily:

talk2=> set timezone to 'America/Chicago';
SET
talk2=> select id, last_login, date_joined from auth_user where id=1;
id | last_login | date_joined
----+-------------------------------+------------------------
1 | 2011-09-05 11:43:38.050294-05 | 2008-05-02 19:29:14-05

Notice the timestamps are different, and have a -05 UTC offset now
instead of a +02.

So if you want non-Django applications to use the DB, you just need
the application to be aware of the UTC offset included in the output,
and apply that accordingly. Or if you prefer, you can indeed just set
the client connect TZ to UTC:

talk2=> set timezone to UTC;
SET
talk2=> select id, last_login, date_joined from auth_user where id=1;
id | last_login | date_joined
----+-------------------------------+------------------------
1 | 2011-09-05 16:43:38.050294+00 | 2008-05-03 00:29:14+00

The main point is that whilst the internal storage of timestamps in
Postgres is UTC Julian dates, the output is always rendered as
localtime for the client connection TZ, whatever that may be.

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Kirit Sælensminde (kayess)]

I just wanted to chime in with a couple of extra notes here. I won't
comment on most of it as I think it's all spot on :)

On Sep 3, 10:40 pm, Aymeric Augustin
<aymeric.augus...-o/5/jSaJEHk+NdeTPqioyti2O/***@public.gmane.org> wrote:
> Django should use timezone-aware UTC datetimes internally
> .........................................................
>
> Example : datetime.datetime(2011, 09, 23, 8, 34, 12, tzinfo=pytz.utc)

There is also a Django time field. How to handle this is kind of hard
as having a timezone on a time field may not make much sense. At the
moment it is up to application code to combine this with a date in a
sane manner. If Django switches to use UTC internally then this is
going to become harder for many users to do correctly. Maybe some
library functions that will help them to do this will work?

Using UTC internally isn't enough in the way that using Unicode
internally is to mean that application writers can factor out time
handling in global applications. For example, if I have a Django
application that tracks retail sales worldwide then a report of sales
figures for any given day probably needs to use the correct local time
at each store to make sense from a business perspective. Using UTC
internally may however make developers aware that there is an issue
for them address earlier -- a good thing.


The other thing that ought to be thought about with this is the admin
time/date widgets which have the 'now' link. This is always filled in
with the browser's current time which plays havoc when admin is used
by a user in a different time zone to the site's settings. It should
be possible to capture the UTC offset along with the time so that the
correct number of minutes is added/subtracted when the field is
processed by Django. Thankfully daylight savings can be ignored here.

Many browsers will send the local time of the request to the server.
This can be used to guess the correct timezone, but it won't get
things right (there's no way to work out the correct DST setting from
this). If the country can be identified in some way then the two
together should be good for most users. The UTC offset in the request
is all that's needed to get localize any times that are sent back
though as again, daylight savings can be ignored -- so long as we
aren't rash enough to presume that this offset tells us the actual
time zone.

Hope this is useful,


Kirit

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Daniel Swarbrick]

On Sep 8, 5:55 am, Kirit Sælensminde (kayess)
<kirit.saelensmi...-***@public.gmane.org> wrote:

> There is also a Django time field. How to handle this is kind of hard
> as having a timezone on a time field may not make much sense. At the
> moment it is up to application code to combine this with a date in a
> sane manner. If Django switches to use UTC internally then this is
> going to become harder for many users to do correctly. Maybe some
> library functions that will help them to do this will work?

Whilst both the Python datetime.time type and the Postgres 'time with
time zone' column type support a TZ, I personally feel that it only
makes sense to attach TZ info for datetime.datetime (eg. 'timestamp
with time zone'). Other people have raised the point before about the
need to retain some way of expressing "wall clock time", and I *think*
separate date and time fields address that issue reasonably well.

Let's say we were to use TZ with simple datetime.time types. A user in
America/Chicago (UTC-05) inputs a time of 18:00. This gets normalized
to UTC and stored as 23:00. A user in Australia/Sydney (UTC+10) wishes
to view that information, and sees the value localized for them as
09:00 - *the next day*. This is why I think it's best to leave TZ out
of simple date or time types, and only use it on datetime types, that
treat the concept of time as a constant, linear progression that is
relative to the observer, not the event that takes place.

The normalization to UTC is really only a factor for databases (or
serializers) that don't already do their own internal storage as UTC.
The application developer should be unaware that it was stored as UTC,
since they are provided a non-naive datetime with tzinfo attached,
which they can then localize to any other TZ they wish.

> The other thing that ought to be thought about with this is the admin
> time/date widgets which have the 'now' link. This is always filled in
> with the browser's current time which plays havoc when admin is used
> by a user in a different time zone to the site's settings. It should
> be possible to capture the UTC offset along with the time so that the
> correct number of minutes is added/subtracted when the field is
> processed by Django. Thankfully daylight savings can be ignored here.

If admins (or users) are inputting localized datetimes, the app will
need to know their timezone, or apply a default system-wide timezone.
I don't see this as much different from the way that the language
cookie is used.

Perhaps a formfield could allow for the specification of a UTC offset
in the input, eg "2011-09-08 16:02+02" and extract that offset from
the input. DST is irrelevant since UTC offset is not the same as
timezone. A location's timezone is the same all year round, but the
UTC offset changes at various times of the year if that timezone
observes DST. That's part of the information provided by the Olson
tzdata database. Usually when a timestamp shows a UTC offset, any DST
offset is included in that UTC offset, so it can still be normalized
to UTC regardless of DST being in effect or not.

> Many browsers will send the local time of the request to the server.
> This can be used to guess the correct timezone, but it won't get
> things right (there's no way to work out the correct DST setting from
> this). If the country can be identified in some way then the two
> together should be good for most users. The UTC offset in the request
> is all that's needed to get localize any times that are sent back
> though as again, daylight savings can be ignored -- so long as we
> aren't rash enough to presume that this offset tells us the actual
> time zone.

The JavaScript Date() object has several methods that could be useful
here, including getTimezoneOffset(), which returns difference between
UTC and local time, in minutes.

Remember that Postgres is not storing the timezone. It's not even
storing the UTC offset. The timezone is only a client connection
"environment var", that gets added/subtracted to timestamps as they
are input or retrieved. Likewise I don't think the objective here is
to store a TZ with the DateTimeField, but rather just to have UTC
offset information available during handling, that facilitates the
normalization to/from UTC for non-TZ-capable databases. Whether that
UTC offset information is in the form a full timezone name, such as
America/Chicago, or simply a UTC offset such as UTC-05, is not
particularly relevant.

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Aymeric Augustin]

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
>

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Aymeric Augustin]

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.

[Aymeric Augustin]

Hello,

This week, I've been working on a related topic that I had missed entirely in my initial proposal: serialization.

Developers will obtain aware datetimes from Django when USE_TZ = True. We must ensure that they serialize correctly.

Currently, the serialization code isn't very consistent with datetimes:
- JSON: the serializer uses the '%Y-%m-%d %H:%M:%S' format, losing microseconds and timezone information. This dates back to the initial commit at r3237. See also #10201.
- XML: the serializer delegates to DateTimeField.value_to_string, who also uses the '%Y-%m-%d %H:%M:%S' format.
- YAML: the serializer handles datetimes natively, and it includes microseconds and UTC offset in the output.

I've hesitated between converting datetimes to UTC or rendering them as-is with an UTC offset. The former would be more consistent with the database and it's recommended in YAML. But the latter avoids modifying the data: not only is it faster, but it's also more predictable. Serialization isn't just about storing the data for further retrieval, it can be used to print arbitrary data in a different format. Finally, when the data comes straight from the database (the common case), it will be in UTC anyway.

Eventually, I've decided to serialize aware datetimes without conversion. The implementation is here:
https://bitbucket.org/aaugustin/django/compare/..django/django

Here are the new serialization formats for datetimes:
- JSON: as described in the specification at http://www.ecma-international.org/publications/files/ECMA-ST/Ecma-262.pdf > 15.9.1.15 Date Time String Format.
- XML: as produced by datetime.isoformat(), ISO8601.
- YAML: unchanged, compatible with http://yaml.org/type/timestamp.html — the canonical representation uses 'T' as separator and is in UTC, but it's also acceptable to use a space and include an offset like pyyaml does.
These formats follow the best practices described in http://www.w3.org/TR/NOTE-datetime.

This fix is backwards-incompatible for the JSON and XML serializers: it includes fractional seconds and timezone information, and it uses the normalized separator, 'T', between the date and time parts. However, I've made sure that existing fixtures will load properly with the new code. I'll mention all this in the release notes.

Unrelatedly, I have switched the SQLite backend to supports_timezones = False, because it really doesn't make sense to write the UTC offset but ignore it when reading back the data.

Best regards,

--
Aymeric Augustin.

On 17 sept. 2011, at 09:59, Aymeric Augustin wrote:

> 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.
>
> <DATABASE-NOTES.html>
>
> 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
>>>
>>
>

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Aymeric Augustin]

Hello,

This week, I've finished the work on serialization by making the deserializers capable of handling UTC offsets. I had to rewrite DateTimeField.to_python to extract and interpret timezone offsets. Still, deserialization of aware datetimes doesn't work with PyYAML: http://pyyaml.org/ticket/202

I also implemented the storage and retrieval of aware datetime objects in PostgreSQL, MySQL and Oracle. Conversions happen:
- on storage, in `connection.ops.value_to_db_datetime`, called from `get_db_prep_value`;
- on retrieval, in the database adapter's conversion functions.
The code is rather straightforward. When USE_TZ is True, naive datetimes are interpreted as local time in TIME_ZONE, for backwards compatibility with existing applications.

SQLite is more tricky because it uses `django.db.backends.util.typecast_timestamp` to convert string to datetimes. However:
- this function is used elsewhere of Django, in combination with the `needs_datetime_string_cast` flag.
- it performs essentially the same operations as `DateTimeField.to_python`.
I'll review the history of this code, and I'll try to refactor it.

Besides adding support for SQLite, I still have to:
- check that datetimes behave correctly when they're used as query arguments, in aggregation functions, etc.
- optimize django.utils.tzinfo: fix #16899, use pytz.utc as the UTC timezone class when pytz is available, etc.

I won't have much time for this project next week. See you in two weeks for the next check-in!

Best regards,

--
Aymeric Augustin.

On 24 sept. 2011, at 15:24, Aymeric Augustin wrote:

> Hello,
>
> This week, I've been working on a related topic that I had missed entirely in my initial proposal: serialization.
>
> Developers will obtain aware datetimes from Django when USE_TZ = True. We must ensure that they serialize correctly.
>
> Currently, the serialization code isn't very consistent with datetimes:
> - JSON: the serializer uses the '%Y-%m-%d %H:%M:%S' format, losing microseconds and timezone information. This dates back to the initial commit at r3237. See also #10201.
> - XML: the serializer delegates to DateTimeField.value_to_string, who also uses the '%Y-%m-%d %H:%M:%S' format.
> - YAML: the serializer handles datetimes natively, and it includes microseconds and UTC offset in the output.
>
> I've hesitated between converting datetimes to UTC or rendering them as-is with an UTC offset. The former would be more consistent with the database and it's recommended in YAML. But the latter avoids modifying the data: not only is it faster, but it's also more predictable. Serialization isn't just about storing the data for further retrieval, it can be used to print arbitrary data in a different format. Finally, when the data comes straight from the database (the common case), it will be in UTC anyway.
>
> Eventually, I've decided to serialize aware datetimes without conversion. The implementation is here:
> https://bitbucket.org/aaugustin/django/compare/..django/django
>
> Here are the new serialization formats for datetimes:
> - JSON: as described in the specification at http://www.ecma-international.org/publications/files/ECMA-ST/Ecma-262.pdf > 15.9.1.15 Date Time String Format.
> - XML: as produced by datetime.isoformat(), ISO8601.
> - YAML: unchanged, compatible with http://yaml.org/type/timestamp.html — the canonical representation uses 'T' as separator and is in UTC, but it's also acceptable to use a space and include an offset like pyyaml does.
> These formats follow the best practices described in http://www.w3.org/TR/NOTE-datetime.
>
> This fix is backwards-incompatible for the JSON and XML serializers: it includes fractional seconds and timezone information, and it uses the normalized separator, 'T', between the date and time parts. However, I've made sure that existing fixtures will load properly with the new code. I'll mention all this in the release notes.
>
> Unrelatedly, I have switched the SQLite backend to supports_timezones = False, because it really doesn't make sense to write the UTC offset but ignore it when reading back the data.
>
> Best regards,
>
> --
> Aymeric Augustin.
>
> On 17 sept. 2011, at 09:59, Aymeric Augustin wrote:
>
>> 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.
>>
>> <DATABASE-NOTES.html>
>>
>> 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
>>>>
>>>
>>
>

--
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-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to django-developers+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Aymeric Augustin]

Le 1 nov. 2011 à 15:58, Aymeric Augustin <***@polytechnique.org> a écrit :
>
> There is only one outstanding issue that I know of. `QuerySet.dates()` operates in the database timezone, ie. UTC, while an end user would expect it to take into account its current time zone. This affects `date_hierarchy` in the admin and the date based generic views. The same problem also exists for the `year`, `month`, `day`, and `week_day` lookups.


Fifteen months later, I finally got around to fixing this. I would appreciate (1) a review (2) tests under Oracle.

Ticket : https://code.djangoproject.com/ticket/17260
Patch: https://github.com/django/django/pull/715

The changes are quite involved — I didn't implement this originally for a reason :)

** Changes **

1) Add a new method QuerySet.datetimes(). This method takes a tzinfo argument that says in which time zone datetimes should be converted prior to truncation and aggregation. It defaults to the current time zone, which will produce the expected results in general.

2) Add plumbing to the ORM to pass the tzinfo argument down to the SQL generation layer, where database specific features are used to perform the time zone conversion within the database.

3) Make QuerySet.dates() return dates instead of datetimes. This is a backwards incompatible change, but:
- I don't want a method called "dates" to return aware datetimes;
- Real code may still work thanks to duck-typing;
- Replacing .dates() with .datetimes() is trivial.

4) Prevent QuerySet.dates() from operating on datetime fields. This is a backwards incompatible change, but:
- The restriction is only enforced when USE_TZ is True;
- It's necessary to stop mixing dates and datetimes unsafely when USE_TZ is True;
- QuerySet.dates() returned incorrect results in 1.4 and 1.5 when USE_TZ was True anyway.

5) Add __hour, __minute and __second lookups, and make all lookups on datetime fields in the current time zone. There's no way to pass an explicit time zone to the lookups, but the current time zone can be overridden if necessary. There's some special handling for __year lookups because they're translated to __range lookups. This is backwards incompatible if someone has fields named hour, minute or second.

** Database support **

- PostgreSQL / PostGIS: everything works.
- SQLite: everything works, requires pytz; Spatialite: untested.
- MySQL / MySQL GIS: everything works, requires loading the time zone definitions.
- Oracle / Oracle GIS: untested.

A database feature called "has_zoneinfo_database" says if the time zone definitions are available.

** Remarks **

There are similar code paths for dates and datetimes, which result in some code duplication. I've tried to minimize it through subclassing wherever possible.

It's weird to add the time zone parameter in SQLDateTimeCompiler.as_sql. This hack is necessary because the ORM doesn't handle parameters for select expressions. For example, SQLCompiler.get_columns returns "sql", unlike get_from_clause which returns "sql, params".

Thanks,

--
Aymeric.



--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[Aymeric Augustin]

I spent the better part of the day struggling with Oracle and unsurprisingly nothing works :(

Given this model:

class Event(models.Model):
dt = models.DateTimeField()

I'm trying to implement this query with time zone support enabled:

Event.objects.filter(dt__month=1)

That's a test from Django's test suite:
https://github.com/aaugustin/django/blob/queryset-datetimes/tests/modeltests/timezones/tests.py#L424

----------------------------------------

I have found a syntax that should work, but it crashes Oracle.

>>> import cx_Oracle
>>> conn = cx_Oracle.connect('django', 'Im_a_lumberjack', cx_Oracle.makedsn('127.0.0.1', 1521, 'orcl'))
>>> c = conn.cursor()
>>> c.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'")
>>> c.execute("""INSERT INTO "TIMEZONES_EVENT" ("DT") VALUES (:arg0)""", ['2010-12-31 22:30:00'])
>>> c.execute("""SELECT FROM_TZ("DT", 'UTC') AT TIME ZONE (:arg) FROM "TIMEZONES_EVENT" """, ['Africa/Nairobi']).fetchall()
[(datetime.datetime(2011, 1, 1, 1, 30),)]

* At this point we have a single object in the database with DT == 2010-12-31 22:30:00 Z == 2011-01-01 01:30:00 +03:00.
* Oracle knows that Africa/Nairobi is +03:00.

>>> c.execute("""SELECT EXTRACT(MONTH FROM CAST((FROM_TZ("DT", 'UTC') AT TIME ZONE (:arg)) AS DATE)) FROM "TIMEZONES_EVENT" """, ['Africa/Nairobi']).fetchall()
[(1,)]

* This expression dutifully extracts the month in local time.
* The time zone name is passed in argument.

>>> c.execute("""SELECT "DT" FROM "TIMEZONES_EVENT" WHERE EXTRACT(MONTH FROM CAST((FROM_TZ("DT", 'UTC') AT TIME ZONE ('Africa/Nairobi')) AS DATE)) = 1""").fetchall()
[(datetime.datetime(2010, 12, 31, 22, 30),)]

* That's almost what I want, but the time zone name is hardcoded. You'll see why in a second.

>>> c.execute("""SELECT "DT" FROM "TIMEZONES_EVENT" WHERE EXTRACT(MONTH FROM CAST((FROM_TZ("DT", 'UTC') AT TIME ZONE (:arg)) AS DATE)) = 1""", ['Africa/Nairobi']).fetchall()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel

* That's exactly the same query as above, except the time zone name is passed in argument.
* Not only does it crash, but it closes the database connection!

Obviously I'm hitting a bug in Oracle. Does anyone have ideas to solve this?

----------------------------------------

One alternative is the use string interpolation to put the time zone name in the query. (It works; that's how I first implemented the feature.)

To avoid SQL injection via this parameter, heavy-handed sanitization of the time zone name will be necessary, like r'^[\w/+-]+$'.

Pro:
- Removes the hack in DateTimeSQLCompiler to inject the time zone parameter.
- Works around the bug in Oracle.

Cons:
- Prevents using arbitrary time zone names. This was discussed at length on the ticket , but I don't know how much of a problem it is in practice. Windows has non-standard, localized time zone names; do databases use them?

--
Aymeric.



--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[Ian Kelly]

On Tue, Feb 12, 2013 at 2:25 PM, Aymeric Augustin
<aymeric.augustin-o/5/jSaJEHk+NdeTPqioyti2O/***@public.gmane.org> wrote:
>>>> c.execute("""SELECT "DT" FROM "TIMEZONES_EVENT" WHERE EXTRACT(MONTH FROM CAST((FROM_TZ("DT", 'UTC') AT TIME ZONE (:arg)) AS DATE)) = 1""", ['Africa/Nairobi']).fetchall()
> Traceback (most recent call last):
> File "<stdin>", line 1, in <module>
> cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel
>
> * That's exactly the same query as above, except the time zone name is passed in argument.
> * Not only does it crash, but it closes the database connection!
>
> Obviously I'm hitting a bug in Oracle. Does anyone have ideas to solve this?

I encounter the same bug in Oracle 10g XE. I tried it also on an 11.2
database, and it seemed to work, but I ran into a different issue:
neither of the time zones 'Africa/Nairobi' nor 'UTC' existed in the
database. I substituted 'Africa/Cairo' and 'Etc/GMT' for the test.
Wondering if that was just a local configuration issue, I tried it
again on another newer 11.2 database. This one did recognize all the
time zones, but also exhibited the connection loss bug.

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[Ian Kelly]

On Tue, Feb 12, 2013 at 3:06 PM, Ian Kelly <ian.g.kelly-***@public.gmane.org> wrote:
> On Tue, Feb 12, 2013 at 2:25 PM, Aymeric Augustin
> <aymeric.augustin-o/5/jSaJEHk+NdeTPqioyti2O/***@public.gmane.org> wrote:
>>>>> c.execute("""SELECT "DT" FROM "TIMEZONES_EVENT" WHERE EXTRACT(MONTH FROM CAST((FROM_TZ("DT", 'UTC') AT TIME ZONE (:arg)) AS DATE)) = 1""", ['Africa/Nairobi']).fetchall()
>> Traceback (most recent call last):
>> File "<stdin>", line 1, in <module>
>> cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel
>>
>> * That's exactly the same query as above, except the time zone name is passed in argument.
>> * Not only does it crash, but it closes the database connection!
>>
>> Obviously I'm hitting a bug in Oracle. Does anyone have ideas to solve this?
>
> I encounter the same bug in Oracle 10g XE. I tried it also on an 11.2
> database, and it seemed to work, but I ran into a different issue:
> neither of the time zones 'Africa/Nairobi' nor 'UTC' existed in the
> database. I substituted 'Africa/Cairo' and 'Etc/GMT' for the test.
> Wondering if that was just a local configuration issue, I tried it
> again on another newer 11.2 database. This one did recognize all the
> time zones, but also exhibited the connection loss bug.

Against the possible issue of non-recognition of UTC, I suggest using
'0:00' instead of 'UTC'.

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[Aymeric Augustin]

Le 12 févr. 2013 à 23:12, Ian Kelly <ian.g.kelly-***@public.gmane.org> a écrit :

> On Tue, Feb 12, 2013 at 3:06 PM, Ian Kelly <ian.g.kelly-***@public.gmane.org> wrote:
>>
>> I encounter the same bug in Oracle 10g XE. I tried it also on an 11.2
>> database, and it seemed to work, but I ran into a different issue:
>> neither of the time zones 'Africa/Nairobi' nor 'UTC' existed in the
>> database. I substituted 'Africa/Cairo' and 'Etc/GMT' for the test.
>> Wondering if that was just a local configuration issue, I tried it
>> again on another newer 11.2 database. This one did recognize all the
>> time zones, but also exhibited the connection loss bug.
>
> Against the possible issue of non-recognition of UTC, I suggest using
> '0:00' instead of 'UTC'.

Thanks for looking into this problem!

I tried '0:00' instead of 'UTC', and '+03:00' instead of
'Africa/Nairobi' with the same result. My first queries
show that conversions are performed correctly
between these two time zones.

When I pass an invalid time zone name I get:
ORA-01882: timezone region not found

I'm testing with the "Oracle Developer Day" Virtual Box VM
(Oracle Database 11g Enterprise Edition Release 11.2.0.2.0)
and v$timezones contains time zone definitions out of the box.

I have cx_Oracle 5.1.2 compiled against Instant Client 10.2
in 32 bits mode (the 11.2 package is broken, and the 64 bits
10.2 package segfaults).

--
Aymeric.



--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[Ian Kelly]

On Tue, Feb 12, 2013 at 3:35 PM, Aymeric Augustin
<aymeric.augustin-o/5/jSaJEHk+NdeTPqioyti2O/***@public.gmane.org> wrote:
>> Against the possible issue of non-recognition of UTC, I suggest using
>> '0:00' instead of 'UTC'.
>
> Thanks for looking into this problem!
>
> I tried '0:00' instead of 'UTC', and '+03:00' instead of
> 'Africa/Nairobi' with the same result. My first queries
> show that conversions are performed correctly
> between these two time zones.
>
> When I pass an invalid time zone name I get:
> ORA-01882: timezone region not found

I didn't mean to suggest this as a fix to the connection dropping
problem. I only meant that the final SQL should use '0:00' instead of
'UTC' to guard against databases that for whatever reason do not have
a UTC time zone definition, such as the one that I found. Otherwise
the query would always fail with an ORA-01882 on those databases.

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[Aymeric Augustin]

Le 13 févr. 2013 à 20:54, Ian Kelly <ian.g.kelly-***@public.gmane.org> a écrit :
> I only meant that the final SQL should use '0:00' instead of
> 'UTC' to guard against databases that for whatever reason do not have
> a UTC time zone definition, such as the one that I found. Otherwise
> the query would always fail with an ORA-01882 on those databases.

Ah -- sorry, I misunderstood. Yes, it's a good idea. Fixed here:
https://github.com/aaugustin/django/commit/4f290bdb60b7d8534abf4ca901bd0844612dcbda

--
Aymeric.



--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[Aymeric Augustin]

I bit the bullet and implemented passing the time zone name:
- via a parameter on PostgreSQL, SQLite and MySQL,
- through string interpolation on Oracle, with a regexp to validate the value.

As a pre-requisite, the ORM needs to handle parameters in the SELECT clause:
https://github.com/aaugustin/django/commit/924a144ef8a80ba4daeeafbe9efaa826566e9d02

Structures that provide an .as_sql() method have been changed to return a
tuple of (sql string, list of params). This includes Aggregate, SQLEvaluator,
and a few others. I fixed some inconsistencies along the way. I also changed
spatial_lookup_sql to this format, because it usually delegates to as_sql.

Note that SQLCompiler.as_sql() returns (sql string, tuple of params) and isn't
involved here.

Then I made tzname a parameter of the database backend's datetime_extract_sql
and datetime_trunc_sql methods:
https://github.com/aaugustin/django/commit/01b6366f3ce67d57a58ca8f25e5be77911748638

The SQL for Oracle isn't very elegant; the final version of the query I
discussed yesterday is:
SELECT "DT" FROM "TIMEZONES_EVENT" WHERE EXTRACT(MONTH FROM CAST(TO_DATE(TO_CHAR((FROM_TZ("TIMEZONES_EVENT"."DT", 'UTC') AT TIME ZONE 'Africa/Nairobi'), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)) = 1

I plan to commit this in a few days; let me know if need more time to review
it!

Thanks.

--
Aymeric.

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To post to this group, send email to django-developers-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.