Datetimes with timezones for mysql

[Vitaly Babiy]

Hey,
I been doing a lot with timezone aware dates and I noticed that the mysql store will not allow you to store a datetime aware datetime. I was wondering if there is a reason why not convert theses dates to whatever is in the settings using astimezone. And then save that date.

Vitaly Babiy

[Russell Keith-Magee]

A good reason? No. :-)

The practical reason is that the current implementation is old and
naive. Django's date/datetime/time fields were added very early in the
life of Django, so the current implementation is essentially the
simplest implementation that could possibly work at the time -- which
means no fancy handling when it comes to timezones.

Adding timezone sensitivity to Django's time/datetime fields is
something that has been on the project to-do list for almost as long
as I've been associated with the project (coming up on 5 years). If
you're interested in tackling this problem, here's a couple of things
to keep in mind:

* Backwards compatibility. Suddenly introducing a 'convert to local
timezone' behavior (or similar) will break any existing install.
* The interaction between application timezone and database server timezone.
* MySQL. Postgres supports timezones in date fields. MySQL doesn't.
We need to have a solid answer for how this feature will work on
databases that don't support timezone handling.

Also worth noting: by strange coincidence, you're the second person in
24 hours that has asked this question; FunkyBob was asking about
timezone support on IRC. This is something I'd love to see addressed;
if there are a couple of motivated people interested in the problem,
it may help to team up to solve it.

Yours,
Russ Magee %-)

[Hanne Moa]

On 17 August 2010 05:24, Russell Keith-Magee <rus...@keith-magee.com> wrote:
> On Tue, Aug 17, 2010 at 12:13 AM, Vitaly Babiy <vbab...@gmail.com> wrote:
>> I been doing a lot with timezone aware dates and I noticed that the mysql
>> store will not allow you to store a datetime aware datetime.
>

> Adding timezone sensitivity to Django's time/datetime fields is
> something that has been on the project to-do list for almost as long
> as I've been associated with the project (coming up on 5 years). If
> you're interested in tackling this problem, here's a couple of things
> to keep in mind:
>
>  * Backwards compatibility. Suddenly introducing a 'convert to local
> timezone' behavior (or similar) will break any existing install.
>  * The interaction between application timezone and database server timezone.
>  * MySQL. Postgres supports timezones in date fields. MySQL doesn't.
> We need to have a solid answer for how this feature will work on
> databases that don't support timezone handling.

As I see it there's two possible usage scenarios of date(time)s.

1. Those who don't need to care about timezones at all, for instance
because no dates are ever shown or shared, or all data will be from a
single timezone that doesn't utilise daylight savings time, or
accuracy better than two hours each way is not needed. This is
unfortunately what Python's datetime-module were designed for.
2. Everybody else.

1 is already solved today. There's more than one way to solve 2.

a) Convert to a single timezone on save (typically UTC but need not
be), and convert to a local timezone when showing. The question then
is whether it is relevant to save the timezone of the original
datetime. This might leak the location of where the input originated.
I currently store everything as UTC and throw away timezone info.
b) Store timezone directly, convert to local timezone when showing.
Then your dates are only as good as your locale-database since
daylight savings start/end tends to be changed by whoever has
political power at the moment.
c) <insert own bikeshed-blueprints here>

HM

[David De La Harpe Golden]

On 17/08/10 04:24, Russell Keith-Magee wrote:

> Adding timezone sensitivity to Django's time/datetime fields is
> something that has been on the project to-do list for almost as long
> as I've been associated with the project (coming up on 5 years).

Just noting existence of Brian Rosner's django-timezones as a datum,
you're very probably already aware of its existence, just mentioning it.

http://github.com/brosner/django-timezones

Not saying it's perfect, just that it exists and helps us (with some
bugfixing) deal with our time zone requirements. But we use postgresql.

We always use django settings.TIME_ZONE = 'UTC', settings.TIME_ZONE is
what django will currently assume - or tell (postgresql "SET TIME ZONE")
the database to use. We _do not_ use settings.TIME_ZONE to determine
what final presentation timezone to use for presentation of our (non
naive via django-timezones) app-internal datetimes to users.

[Vitaly Babiy]

Well what I was thinking is that your settings.TIME_ZONE can be set to what ever you want. If we get a datetime that is timezone aware we covert it to what your settings say. If a datetime is not timezone aware we just save it to the database. And when you pull the object out of the database the dates will all be what your settings timezone is set to. I would need help with out PostgreSQL side of things since I have never used it, and other DB testing.

Also on a side note I think it would be helpful to have the ORM return all datetime with the tzinfo set to whatever the settings are. That way when you need to mess with the timezone you don't have to do something like this for every datetime you need to adjust:

utc_date = obj.created_at.replace(tzinfo=pytz.UTC)

About backward compatibility:

I don't think introducing this will cause any problems since if you pass the ORM a datetime that is naive it will just assume that it is the correct timezone.

Thanks,

Vitaly Babiy

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-d...@googlegroups.com.
To unsubscribe from this group, send email to django-develop...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.