Convert datetime to unix timestamp in SQLAlchemy model

Last Updated : 24 Apr, 2025

When dealing with databases date and time are considered to be one of the most important attributes for any entity. With such data, we often encounter some common task of converting DateTime to a Unix timestamp. In this article, we will learn how we can convert datetime to Unix timestamp in SQLAlchemy.

Converting Datetime to Unix TimeStamp in SQLalchemy Model

Before moving to the demonstration let's see an overview of a few tools that we will be using in this article.

Python is an object-oriented, dynamically typed, OpenSource programming language that is used for a variety of software development projects. SQLalchemy is basically referred to as the toolkit of Python SQL that provides developers with the flexibility of using the SQL database. It is a popular Python Object Relational Mapper (ORM) that provides developers with all of the abilities and functionality of SQL using Python as a language.
Unix timestamp is a way of measuring the time that is profoundly used by computers. A Unix timestamp is a number something like `1683052688000` which represents the total time in seconds from the Unix Epoch (January 1st, 1970 at UTC).

Steps to Convert Datetime to Unix Timestamp

Now that we have a decent understanding of all tools let's move to the demonstration which can be divided into the following sections,

Importing the SQLalchemy module.
Implement a custom DateTime type for the SQLAlchemy model.
Define a User model which will use the custom-defined UNIX timestamp type
We will insert some data into the database table
Lastly, we will show the output with a UNIX timestamp.

Let's start with the demonstration.

Importing the SQLalchemy Module

First of all, we will import all the required modules. The 'TypeDecorator is used for creating custom data types. The 'create_engine' will be used to create a database engine. The 'sessionmaker' and 'declarative_base' are used for creating SQLalchemy sessions and models, respectively. Python datetime module is used to work with date and time.

Python3

# import required modules
from sqlalchemy.types import TypeDecorator
from sqlalchemy import Integer, String, Column
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
import datetime

Implement a Custom Date Time type as `UnixTimeStamp`

TypeDecorator is a class provided by SQLalchemy that allows us to define a custom type for SQLalchemy models. We will create a custom type `UnixTimestamp` class by inheriting the `TypeDecorator' which is used as a base class. It contains a method `process_bind_param()` that will take a human format date as input and then returns a Unix timestamp, So when we insert any date and time data into the database it will first be passed through the `process_bind_param()` method, converted to a UNIX timestamp, and then will be sent to the database.

Python3

# define a custom type for unix timestamp
class UnixTimestamp(TypeDecorator):

    # convert unix timestamp to datetime object
    impl = Integer

    # convert datetime object to unix timestamp when inserting data to database
    def process_bind_param(self, value, dialect):
        if value is not None:
            return int(value.timestamp())
        else:
            return None

# create a base class for the models Base = declarative_base() # create a model for the user class User(Base): __tablename__ = 'users' # define the columns of the user table id = Column(Integer, primary_key=True) name = Column(String(50)) # using the custom defined type for created_at column created_at = Column(UnixTimestamp, default=datetime.datetime.utcnow()) # used while printing the user object from the database def __repr__(self): return f"User(id={self.id}, name={self.name}, created_at={self.created_at})"

from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker # commiting a user to database with created_at in unix timestamp user = User(name='John') # create an engine engine = create_engine('sqlite:///unix_timestamp.db', echo=True) # create the table Base.metadata.create_all(engine) # create a session Session = sessionmaker(bind=engine) session = Session() # add the user to the session session.add(user) # commit the session session.commit()