SQLAlchemy default DateTime

by

Calculate timestamps within your DB, not your client

For sanity, you probably want to have all datetimes calculated by your DB server, rather than the application server. Calculating the timestamp in the application can lead to problems because network latency is variable, clients experience slightly different clock drift, and different programming languages occasionally calculate time slightly differently.

SQLAlchemy allows you to do this by passing func.now() or func.current_timestamp() (they are aliases of each other) which tells the DB to calculate the timestamp itself.

Use SQLALchemy’s server_default

Additionally, for a default where you’re already telling the DB to calculate the value, it’s generally better to use server_default instead of default. This tells SQLAlchemy to pass the default value as part of the CREATE TABLE statement.

For example, if you write an ad hoc script against this table, using server_default means you won’t need to worry about manually adding a timestamp call to your script–the database will set it automatically.

Understanding SQLAlchemy’s onupdate/server_onupdate

SQLAlchemy also supports onupdate so that anytime the row is updated it inserts a new timestamp. Again, best to tell the DB to calculate the timestamp itself:

from sqlalchemy.sql import func

time_created = Column(DateTime(timezone=True), server_default=func.now())
time_updated = Column(DateTime(timezone=True), onupdate=func.now())

There is a server_onupdate parameter, but unlike server_default, it doesn’t actually set anything serverside. It just tells SQLalchemy that your database will change the column when an update happens (perhaps you created a trigger on the column ), so SQLAlchemy will ask for the return value so it can update the corresponding object.

One other potential gotcha:

You might be surprised to notice that if you make a bunch of changes within a single transaction, they all have the same timestamp. That’s because the SQL standard specifies that CURRENT_TIMESTAMP returns values based on the start of the transaction.

PostgreSQL provides the non-SQL-standard statement_timestamp() and clock_timestamp() which do change within a transaction. Docs here: https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

UTC timestamp

If you want to use UTC timestamps, a stub of implementation for func.utcnow() is provided in SQLAlchemy documentation. You need to provide appropriate driver-specific functions on your own though.

More Related Contents:

  1. Add missing dates to pandas dataframe
  2. Creating a range of dates in Python
  3. Convert Excel style date with pandas
  4. Locale date formatting in Python
  5. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  6. SQLAlchemy IN clause
  7. Flask SQLAlchemy query, specify column names
  8. Find the end of the month of a Pandas DataFrame Series
  9. How can I bind a list to a parameter in a custom query in SQLAlchemy?
  10. How to disable SQLAlchemy caching?
  11. Generate a list of datetimes between an interval
  12. Sort a pandas dataframe series by month name
  13. sqlalchemy unique across multiple columns
  14. How to create a new database using SQLAlchemy?
  15. Multi-threaded use of SQLAlchemy
  16. How to get column names from SQLAlchemy result (declarative syntax)
  17. How to discover table properties from SQLAlchemy mapped object
  18. IntegrityError when inserting data in an association table using SQLAlchemy
  19. Days between two dates? [duplicate]
  20. Extracting dates that are in different formats using regex and sorting them – pandas
  21. Best way to do enum in Sqlalchemy?
  22. How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?
  23. Creating partial unique index with sqlalchemy on Postgres
  24. SQLAlchemy Joinedload filter column
  25. SQLAlchemy func.count on boolean column
  26. How to set connection timeout in SQLAlchemy
  27. SQLAlchemy classes across files
  28. Convert dates to pd.to_datetime where month could be either a number or month name
  29. How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?
  30. flask sqlalchemy query with keyword as variable

Leave a Comment