SQLAlchemy server-side datetime calculations
Why you should try to avoid client-side datetime calculations with SQLAlchemy unless ...
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.
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.
I just noticed the post above immediately displayed as posted 1 hour ago . . . I'm on GMT (London).
Me again! But after posting the second post, the first changed to "2 minutes ago", the second started at "0 seconds ago"!
Then after the third post all three now say "1 hour" . . . had to wait 5 minutes to post this one :-)
Thank you for reporting this. I am still working on many parts of this website ... will fix this soon.
Recent
- Hiding database UUID primary keys of your web application
- Don't Repeat Yourself (DRY) with Jinja2
- SQLAlchemy, PostgreSQL, maximum number of rows per user
- Show the values in SQLAlchemy dynamic filters
- Secure data transfer with Public Key encryption and pyNaCl
- rqlite: a high-availability and distributed SQLite alternative
Most viewed
- Using Python's pyOpenSSL to verify SSL certificates downloaded from a host
- Using UUIDs instead of Integer Autoincrement Primary Keys with SQLAlchemy and MariaDb
- Connect to a service on a Docker host from a Docker container
- Using PyInstaller and Cython to create a Python executable
- SQLAlchemy: Using Cascade Deletes to delete related objects
- Flask RESTful API request parameter validation with Marshmallow schemas