Sling Academy
Home/SQLAlchemy/How to use DATEADD in SQLAlchemy

How to use DATEADD in SQLAlchemy

Last updated: January 03, 2024

Introduction

Understanding how to manipulate dates and times in a database is essential for a variety of applications. This tutorial goes through the use of DATEADD in SQLAlchemy, to help you efficiently manage datetime arithmetic in your data queries.

Basics of DATEADD

The DATEADD function is used to add a specified time interval to a date or datetime expression. It’s commonly available in SQL-based databases like MS SQL Server and sometimes in a slightly different form in others such as PostgreSQL and MySQL.


from sqlalchemy import create_engine, Column, Integer, Date, select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import func

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    event_date = Column(Date)

# Create an engine to the database
engine = create_engine('sqlite:///:memory:')

# Create session
Session = sessionmaker(bind=engine)
session = Session()

# Define DATEADD equivalent
from datetime import timedelta

def dateadd(date_field, interval):
    return date_field + interval

# Applying DATEADD in a query
result = session.query(MyTable.event_date, dateadd(MyTable.event_date, timedelta(days=7))).all()

print(result)

Working with Different Databases

In many databases, DATEADD is not a native function, and SQLAlchemy does not have a built-in ‘dateadd’. Here is how you can handle it with different databases:

SQL Server


# SQL Server supports DATEADD natively
from sqlalchemy import text
result = session.query(
    text("DATEADD(day, 7, event_date) AS event_date_plus_7"
)).filter(MyTable.id == 1).all()

PostgreSQL


# Using PostgreSQL, you can use an equivalent operation
result = session.query(
    MyTable.event_date, 
    (MyTable.event_date + text('interval 7 day')).label('event_date_plus_7')
).filter(MyTable.id == 1).all()

MySQL


# In MySQL, the equivalent is achieved with DATE_ADD
result = session.query(
    text("DATE_ADD(event_date, INTERVAL 7 DAY) AS event_date_plus_7")
).filter(MyTable.id == 1).all()

More Complex Interval Arithmetic

Beyond simply adding days, DATEADD allows for more sophisticated interval calculations.


# For example, adding years, months, and minutes
result = session.query(
    MyTable.event_date, 
    (MyTable.event_date + text("interval '2 years 3 months' 15 minute")).label('new_event_date')
).filter(MyTable.id == 1).all()

Joining Tables with DATEADD

When working with joins, you can also use DATEADD in conjunction with the on clause.


# Example of a join with DATEADD
result = session.query(MyTable).join(
    OtherTable, 
    OtherTable.some_date == dateadd(MyTable.event_date, timedelta(days=7))
).all()

Using DATEADD with Filters

Filters can also take advantage of DATEADD to limit the scope of queries based on date calculations.


# Applying filters with DATEADD
three_days_ago = dateadd(func.now(), timedelta(days=-3))
result = session.query(MyTable).filter(MyTable.event_date >= three_days_ago).all()

Conclusion

This tutorial aimed to provide you with a grasp of using DATEADD in SQLAlchemy through a wide range of scenarios. With this knowledge, you can now perform complex date and time calculations within your SQL queries using SQLAlchemy to drive data-driven applications effectively.

Next Article: Fixing SQLAlchemy Error – MissingGreenlet: greenlet_spawn not called

Previous Article: LEFT OUTER JOIN in SQLAlchemy

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