angle-up arrow-clockwise arrow-counterclockwise arrow-down-up arrow-left at calendar card-list chat check envelope folder house info-circle pencil people person person-plus phone plus question-circle search tag trash x

SQLAlchemy server-side datetime calculations

24 June 2019 Updated 31 August 2019 by Peter

Why you should try to avoid client-side datetime calculations with SQLAlchemy unless ...

post main image
Original photo unsplash.com/@nputra.

You will find a lot of SQLAlchemy datetime calculation examples using e.g. Python's timedelta function. Why? I do not understand this except that this is easy. But is it correct? 

Assume we we want all user records or objects created two hours ago and the record / object definition is: 

class User(Base):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    created_on = Column(DateTime, server_default=func.now(), index=True)
    email = Column(String(100), server_default='', index=True)

Then if we could use Python to select the records / objects added the last 10 minutes, we could do something like this :

from datetime import datetime, timedelta

now = datetime.now()
two_hours_ago = now - timedelta(hours=2)

# return all users created less then 2 hours ago
db.query(User).filter(User.created_on > two_hours_ago).all()

The generated SQL is:

SELECT user.id AS user_id, user.created_on AS user_created_on, user.email AS user_email
FROM user 
WHERE user.created_on > %(created_on_1)s
INFO sqlalchemy.engine.base.Engine {'created_on_1': datetime.datetime(2019, 6, 25, 7, 31, 58, 630959)}

This only works, gives valid results, if:

  • the database server is running on the same server where the Python code is running
  • the database server is running on a different server from the the Python code server and the time on both servers is perfectly synchronized. 

Assume you have a separate database server and the time of this server is 2 minutes out-of-sync. Then you get wrong, incomplete  results. I have been writing server -side queries for many years and am surprised there is little attention for this in SQLAlchemy questions and answers. 

The only way way to get the proper results is by using the datetime stamps of the database server records and adding datetime to or subtracting datetime from them. With MariaDB / MySQL you can use the interval statement:

SELECT user.* FROM user WHERE created_on > (NOW() - INTERVAL 2 HOUR)

Unfortunately I could not find a solution for SQLAlchemy that would be valid for all databases. SQLAlchemy has the text() object, it passes the value to the query. With text(), the SQLAlchemy query becomes:

from sqlalchemy import text

two_hours_ago = text('NOW() - INTERVAL 2 HOURS')

# return all users created less then 2 hours ago
db.query(User).filter(User.created_on > two_hours_ago).all()

The generated SQL is:

SELECT user.id AS user_id, user.created_on AS user_created_on, user.email AS user_email
FROM user 
WHERE user.created_on > NOW() - INTERVAL 2 HOUR

Be aware that this query may not work on all database systems. It works with MariaDB / MySQL but It certainly does not work with SQLite.

If you develop an application and run everything on a one computer then always keep in mind that in future you may want to run the database on a separate server. So it is not a bad idea to develop your queries for this situation.    

Links / credits

Flask-sqlalchemy query datetime intervals
https://stackoverflow.com/questions/30495935/flask-sqlalchemy-query-datetime-intervals

SQLAlchemy datetime operations on server side
https://stackoverflow.com/questions/12540175/sqlalchemy-datetime-operations-on-server-side

SQLAlchemy default DateTime
https://stackoverflow.com/questions/13370317/sqlalchemy-default-datetime

Using DATEADD in sqlalchemy
https://stackoverflow.com/questions/15572292/using-dateadd-in-sqlalchemy/15573750#15573750

Read more:
SQLAlchemy

Leave a comment

Comment anonymously or log in to comment.

Comments

Leave a reply

Reply anonymously or log in to reply.