Why is my created date later than my updated date when I create a new row using SQLAlchemy with a Postgres database?

Question

The title says it all but I am confused why when using this SQL Alchemy model I get a created date later than my updated date.

I ran this code at 08:57:53 and created says 2023-06-23 08:57:53.000 and updated says 2023-06-23 07:57:41.670.

I am assuming it's to do with the server determining the update time but I would like them aligned to the same time zone.

What have I missed here as I'm pretty sure I didn't travel back in time by an hour and 12 seconds?

class Test(db.Model):
    __tablename__ = 'tests'
    id = db.Column(db.Integer, primary_key=True)
    uuid = db.Column(db.String(36), index=True, default=lambda:str(uuid.uuid4()))
    payload = db.Column(MutableDict.as_mutable(JSONB))
    processed = db.Column(db.Boolean)
    created = db.Column(db.DateTime, default=dt.utcnow(), nullable=False)
    updated = db.Column(db.DateTime, default=dt.utcnow(), onupdate=dt.utcnow(), nullable=False)

With:

session = db.Session()
new_test = Test(
    payload = payload,
    processed = False,
    created = dt.fromtimestamp(processed_timestamp)
)
db.session.add(new_test)
db.session.commit()
db.session.close()

Answer 1

Edit as per the comments

This is happening because of the way you set the value for created and the default value for onupdate.

time.time() returns the time since epoch but when you do dt.fromtimestamp(processed_timestamp) it gets converted into a naive datetime as per your local timezone which happens to be BST in your case.

dt.utcnow() returns the current UTC time, and as you know, BST is an hour ahead of UTC, which explains the difference you are seeing.

You have to ensure you use the same timezone before you populate, either use UTC (utcfromtimestamp) or BST (fromtimestamp) as per your application needs.

With that being said, the earlier suggestion still stands, which is a problem you might face later.

This is because you are calling dt.utcnow function here default=dt.utcnow(). Chances are you started your process around 2023-06-23 07:57:41.670 and that is the value returned that was set as default.

You should instead do default=dt.utcnow without () like so.

created = db.Column(db.DateTime, default=dt.utcnow, nullable=False)
updated = db.Column(db.DateTime, default=dt.utcnow, onupdate=dt.utcnow, nullable=False)