angle-uparrow-clockwisearrow-counterclockwisearrow-down-uparrow-leftatcalendarcard-listchatcheckenvelopefolderhouseinfo-circlepencilpeoplepersonperson-fillperson-plusphoneplusquestion-circlesearchtagtrashx

SQLAlchemy server-side datetime calculations

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

24 June 2019 Updated 31 August 2019
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 (5)

Leave a reply

Reply anonymously or log in to reply.

avatar

Another great post. I have to agree it is surprising most solutions on the web ignore the potential Python app versus DBMS clock difference problem. The kind of bug that waits patiently until its time!
FWIW `sqlalchemy.sql.expression.func.now() - timedelta(minutes=2)` seems to work for Postgres and I would expect others with a NOW() function.

avatar
Anonymous visitor (not logged in) 3 years ago Anonymous visitor (not logged in)

I just noticed the post above immediately displayed as posted 1 hour ago . . . I'm on GMT (London).

avatar
Anonymous visitor (not logged in) 3 years ago Anonymous visitor (not logged in)

Me again! But after posting the second post, the first changed to "2 minutes ago", the second started at "0 seconds ago"!

avatar
Anonymous visitor (not logged in) 3 years ago Anonymous visitor (not logged in)

Then after the third post all three now say "1 hour" . . . had to wait 5 minutes to post this one :-)

avatar
peter 3 years ago Anonymous visitor (not logged in)

Thank you for reporting this. I am still working on many parts of this website ... will fix this soon.