DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • MySQL Multi-Source Replication
  • An In-Depth Look at Oracle MySQL HeatWave
  • SQL Query Performance Tuning in MySQL
  • Kubernetes Evolution: Transitioning from etcd to Distributed SQL

Trending

  • Development of Custom Web Applications Within SAP Business Technology Platform
  • Edge Data Platforms, Real-Time Services, and Modern Data Trends
  • Beyond the Prompt: Unmasking Prompt Injections in Large Language Models
  • LLMs for Bad Content Detection: Pros and Cons
  1. DZone
  2. Data Engineering
  3. Databases
  4. Django With Time Zone Support and MySQL

Django With Time Zone Support and MySQL

Peter Zaitsev user avatar by
Peter Zaitsev
·
Jan. 21, 15 · Interview
Like (0)
Save
Tweet
Share
8.81K Views

Join the DZone community and get the full member experience.

Join For Free

Originally Written by Roman Vynar

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!

MySQL Django (web framework)

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • MySQL Multi-Source Replication
  • An In-Depth Look at Oracle MySQL HeatWave
  • SQL Query Performance Tuning in MySQL
  • Kubernetes Evolution: Transitioning from etcd to Distributed SQL

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: