Sling Academy
Home/SQLAlchemy/SQLAlchemy: How to store datetime with timezone

SQLAlchemy: How to store datetime with timezone

Last updated: January 03, 2024

Introduction

When working with databases, it is essential to handle datetime values properly, especially when dealing with multiple timezones. This tutorial explores how to store and work with timezone-aware datetime objects in SQLAlchemy, ensuring your applications correctly handle time-based data.

Understanding Timezones in Python

Before diving into SQLAlchemy, it is crucial to understand how Python handles datetimes and timezones. The standard datetime module in Python allows for timezone-aware and naive datetime objects. To include timezones, Python relies on the third-party pytz module or the standard library’s timezone class in Python 3.9 and newer, which provides a concrete implementation of a tzinfo object.

from datetime import datetime, timezone
import pytz

# Naive datetime
naive_dt = datetime.now()

# Timezone-aware datetime
aware_dt = datetime.now(timezone.utc)

# Using pytz
eastern = pytz.timezone('US/Eastern')
aware_dt_with_pytz = datetime.now(eastern)

Setting Up Your SQLAlchemy Environment

You must first install SQLAlchemy and a database adapter, such as psycopg2 for PostgreSQL. Then, define your database engine and create a Base class that will serve as the declarative base for your models.

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Database connection string
DATABASE_URL = "postgresql+psycopg2://user:password@localhost/dbname"

# Create the SQLAlchemy engine
engine = create_engine(DATABASE_URL)

# Create a declarative base
Base = declarative_base()

# Create a session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Defining Models with Timezone-Aware Datetime Fields

In defining a SQLAlchemy column for datetimes, use the DateTime field along with the timezone argument and set it to True to create a timezone-aware column.

from sqlalchemy import Column, Integer, DateTime

class Event(Base):
    __tablename__ = 'events'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    timestamp = Column(DateTime(timezone=True))

Storing Timezone-Aware Datetimes

To handle datetimes with timezones properly, always store them in UTC and convert to the desired timezone upon retrieval. Storing in UTC ensures consistency across different timezones.

# Store a UTC datetime
from datetime import datetime, timezone

def create_event(name, tz_aware_datetime):
    db = SessionLocal()
    event = Event(name=name, timestamp=tz_aware_datetime)
    db.add(event)
    db.commit()
    db.close()

naive_dt = datetime.now()
aware_dt = naive_dt.astimezone(timezone.utc)
create_event('New Year', aware_dt)

Querying Timezone-Aware Datetimes

To retrieve the datetimes, query them directly and convert to the intended timezone if necessary. With SQLAlchemy’s ORM, querying is straightforward.

# Query an event

# Note that you should handle your session lifecycle properly, this is just a simplified example
def get_event(event_id):
    db = SessionLocal()
    event = db.query(Event).filter(Event.id == event_id).first()
    db.close()
    return event

# Convert UTC datetime to a specific timezone
from pytz import timezone
def convert_to_est(utc_dt):
    eastern = timezone('US/Eastern')
    return utc_dt.astimezone(eastern)

# Retrieve and convert the event's datetime

event = get_event(1)
local_time = convert_to_est(event.timestamp)

Advanced Usage: Custom Timezone Handling

If you need to accommodate users in various timezones, you’ll want to add functionality that allows them to specify their timezone preferences and then convert retrieved timestamps accordingly.

def get_user_event_in_timezone(event_id, user_timezone_str):
    db = SessionLocal()
    event = db.query(Event).filter(Event.id == event_id).first()
    user_timezone = pytz.timezone(user_timezone_str)
    event.timestamp = event.timestamp.astimezone(user_timezone)
    db.close()
    return event

Dealing with Daylight Saving Time (DST)

Correctly handling DST can be tricky. The key is to store all datetimes in UTC and use timezone objects that are DST-aware, like those provided by pytz, when making conversions. Always keep your pytz library up-to-date to respect the latest DST rules.

Migrations and Schema Changes

Moving from naive to timezone-aware datetimes can require database migrations, especially if you’re already using a DATETIME type without timezone information. During migrations, convert naive datetime records to UTC or a specific timezone as needed.

Handling Timezones with SQLAlchemy Core

The use of SQLAlchemy ORM is common, but if you’re using the SQLAlchemy Core, similar principles apply. Define datetime columns with timezone information and handle conversions manually when storing and retrieving records.

from sqlalchemy import Table, MetaData, Column, DateTime

metadata = MetaData()
events = Table('events', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String),
              Column('timestamp', DateTime(timezone=True)))

Conclusion

In this comprehensive guide, we’ve tackled how to manage timezone-aware datetimes in SQLAlchemy, providing consistency and accuracy in handling time-based data. We’ve moved from basic implementations to more advanced concepts, such as taking into account user-specific timezones and DST complexities. By following these best practices, your application’s date and time management will be robust and ready for global users.

Next Article: How to filter results by multiple columns in SQLAlchemy

Previous Article: SQLAlchemy: Select rows where column is Null or empty

Series: SQLAlchemy Tutorials: From Basic to Advanced

SQLAlchemy

You May Also Like

  • SQLAlchemy: Counting rows for each category (2 approaches)
  • SQLAlchemy: Adding a calculated column to SELECT query
  • SQLAlchemy: Grouping data on multiple columns
  • SQLAlchemy: How to temporarily delete a row
  • SQLAlchemy Composite Indexes: The Complete Guide
  • Full-Text Search in SQLAlchemy: The Ultimate Guide
  • SQLAlchemy: What if you don’t close database connections?
  • SQLAlchemy: How to Remove FOREIGN KEY Constraints (2 Ways)
  • SQLAlchemy: How to Create and Use Temporary Tables
  • SQLAlchemy: Saving Categories and Subcategories in the Same Table
  • SQLAlchemy: How to Automatically Delete Old Records
  • Weighted Random Selection in SQLAlchemy: An In-Depth Guide
  • SQLAlchemy: created_at and updated_at columns
  • How to Use Regular Expressions in SQLAlchemy
  • SQLAlchemy: Ways to Find Results by a Keyword
  • SQLAlchemy: How to Connect to MySQL Database
  • SQLAlchemy: How to Bulk Insert Data into a Table
  • SQLAlchemy: How to Update a Record by ID
  • SQLAlchemy: Singular vs Plural Table Names