This is yet another story of Django web-framework with time zone support and pain dealing with python datetimes and MySQL on the backend. In other words, offset-naive vs offset-aware datetimes.
Shortly, more about the problem. After reading the official documentation about the time zones, it makes clear that in order to reflect python datetime in the necessary time zone you need to make it tz-aware first and than show in that time zone.
Here is the first issue: tz-aware in what time zone? MySQL stores timestamps in UTC and converts for storage/retrieval from/to the current time zone. By default, the current time zone is the server’s time, can be changed on MySQL globally, per connection etc. So it becomes not obvious what was tz of the value initially before stored in UTC. If you change server or session tz further, it will lead to more mess. Unlike MySQL, PostgreSQL has timestamp with time zone data type, so Django can auto-detect tz and make datetimes tz-aware automatically featuring tzinfo attribute.
There are many solutions on the web… for example an extension to detect tz on UI by Javascript and pass back to the backend allowing you to work with tz-aware data, however, I need something simpler but mainly with less changes to the existing code base.
Here is my case. The server and MySQL are on UTC. That’s cool and it cuts off the first barrier. I store python datetimes in MySQL timespamp columns also in UTC per database time. Anyway, it is a best practice to store time in UTC. I have some read-only pages on the web app and want to show datetimes according to user’s tz. Looks to be a simple task but dealing with MySQL on backend, all my data coming from models have a naive datetime type assigned. So I need to find a way to easily make all my DateTimeField fields UTC-aware (add tzinfo attribute) and some convenient method of showing datetimes in user’s tz still having an access to UTC or naive datetimes for calculation on the backned. Therefore, I will be still doing all the calculation in UTC and showing TZ-aware values to users only on UI side.
This is an example of middleware that gets user’s tz from the database and sets in the session, so it can be retrieved anywhere using get_current_timezone():
from django.utils.timezone import activate
from myapp.models import UserInfo
class TimezoneMiddleware(object):
"""Middleware that is run on each request before the view is executed.
Activate user's timezone for further retrieval by get_current_timezone() or
creating tz-aware datetime objects beforehand.
"""
def process_request(self, request):
session_tz = request.session.get('timezone')
# If tz has been already set in session, let's activate it
# and avoid SQL query to retrieve it on each request.
if session_tz:
activate(session_tz)
else:
try:
# Get user's tz from the database.
uinfo = UserInfo.objects.get(user_id=request.user.id, user_id__isnull=False)
if uinfo.timezone:
# If tz is configured by user, let's set it for the session.
request.session['timezone'] = uinfo.timezone
activate(uinfo.timezone)
except UserInfo.DoesNotExist:
pass
This is an excerpt from models.py:
from django.db import models
from django.utils.timezone import get_current_timezone, make_aware, utc
def localize_datetime(dtime):
"""Makes DateTimeField value UTC-aware and returns datetime string localized
in user's timezone in ISO format.
"""
tz_aware = make_aware(dtime, utc).astimezone(get_current_timezone())
return datetime.datetime.strftime(tz_aware, '%Y-%m-%d %H:%M:%S')
class Messages(models.Model):
id = models.AutoField(primary_key=True)
body = models.CharField(max_length=160L)
created = models.DateTimeField(auto_now_add=True)
@property
def created_tz(self):
return localize_datetime(self.created)
...
“Messages” model has “created” field (timestamp in MySQL) and a property “created_tz”. That property reflects “created” in user’s tz using the function localize_datetime() which makes naive datetimes tz(UTC)-aware, converts into user’s tz set on the session level and returns a string in ISO format. In my case, I don’t prefer the default RFC format that includes +00:00 tz portion of datetime with tzinfo attribute or even need tz-aware datetimes to operate with. Same way I can have similar properties in all needed models knowing they can be accessed by the same name with “_tz” suffix.
Taking into account the above, I reference “created” for calculations in views or controllers and “created_tz” in templaetes or for JSON-output. This way I don’t need to change all references of “created” to something like “make_aware(created, utc)” or datetime.datetime.utcnow() to datetime.datetime.utcnow().replace(tzinfo=pytz.utc) across the code. The code changes in my app will be minimal by introducing a custom property in the model and continue operating with UTC on the raw level:
# views.py
# Operating UTC
msgs = Messages.objects.filter(created__gt=datetime.datetime.now() -
datetime.datetime.timedelta(hours=24))
<! -- HTML template -- >
{% for m in msgs %}
{{ m.id }}. {{ m.body }} (added on {{ m.created_tz }})
{% endfor %}
* All times in user's tz.
I hope this article may help in your findings.
Happy New Year across all time zones!
The post Django with time zone support and MySQL appeared first on MySQL Performance Blog.