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 modulesfrom sqlalchemy.types import TypeDecoratorfrom sqlalchemy import Integer, String, Columnfrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmaker, declarative_baseimport 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 timestampclass 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 modelsBase = declarative_base()# create a model for the userclass 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_enginefrom sqlalchemy.orm import sessionmaker# commiting a user to database with created_at in unix timestampuser = User(name='John')# create an engine# create the tableBase.metadata.create_all(engine)# create a sessionSession = sessionmaker(bind=engine)session = Session()# add the user to the sessionsession.add(user)# commit the sessionsession.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 databaseusers = session.query(User).all()# print the usersfor 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
