Convert Datetime To Unix Timestamp In Sqlalchemy Model Before Executing Query?

I am using SQLAlchemy to work with a remote database that uses a strange timestamp format--it stores timestamps as double-precision milliseconds since epoch. I'd like to work with

Solution 1:

You have to use a custom type, which isn't as scary as it sounds.

from sqlalchemy.types import TypeDecorator


classDoubleTimestamp(TypeDecorator):
    impl = DOUBLE

    def__init__(self):
        TypeDecorator.__init__(self, as_decimal=False)

    defprocess_bind_param(self, value, dialect):
        return value.replace(tzinfo=datetime.timezone.utc).timestamp() * 1000defprocess_result_value(self, value, dialect):
        return datetime.datetime.utcfromtimestamp(value / 1000)

Then Table becomes:

class Table(Base):
    __tablename__ = "table"

    id =Column(Integer, primary_key=True)
    timestamp=Column(DoubleTimestamp)

And then everything you mention works. You insert, select and compare with datetimes but it's stored as a DOUBLE.

Here I've used different logic for converting between timestamps since strftime('%s') isn't the correct solution. It's a different question which has been answered correctly here. Oh and I noticed you said microseconds but only convert to milliseconds in the code you posted, unless it was a slip of the tongue 😉.