Convert datetime to unix timestamp in SQLAlchemy model

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,

1. Importing the SQLalchemy module.
2. Implement a custom DateTime type for the SQLAlchemy model.
3. Define a User model which will use the custom-defined UNIX timestamp type 
4. We will insert some data into the database table
5. 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

Defining an SQLAlchemy Model and Consuming the custom type 

Now we will use the custom type class created before in our models to set the column type to Unix timestamp. We will create an SQLAlchemy model named `User` with id, name, and created_at attributes which is of `UnixTimestamp` type. When a user entity will be inserted into the database the created_at attribute will be converted to a UNIX timestamp and will be saved to the database.

Python3

# 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})"

Using the Above Defined `User` model and Inserting data into the database

Now that we are done defining the model, we will insert a few users into the database to test the model. So first we create a user object then created a database engine that creates the database and connects to it. Once the connection is established we create a database session using the ‘sessionmaker’ provided by SQLalchemy and using it commit the newly created user to the database.

Python3

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()

Output:

Over here we have created a demo user named ‘John’, then created an SQLite DB named `unix_timestamp`, and finally saved the user to the DB. 

unix_timestamp database

data is been inserted with UNIX timestamp into the SQLite database as seen below in the SQLite viewer.

SQLite viewer

Print all Users with Unix Timestamp 

Now, query the database now to get all created users which will contain the UNIX timestamp field, This will return all users in the databases.

Python3

# query the database
users = session.query(User).all()

# print the users
for user in users:
    print(user)

Output:

The highlighted part is the user Inserted into the database with name as string and created_at as Unix timestamp field.

All users of the unix_timestamp database