Converting string to date timestamp in SQLAlchemy

Question

I have a database table, which I am accessing through SQLAlchemy. Each row is timestamped, but unfortunately it isn't simply a column with a DATE type.

It is timestamped with two columns. One is the date as a string (not in the ISO format "YYYY-MM-DD" format, but in the Commonwealth "DD/MM/YYYY" format.) and the other is the time as a string.

I could get the data into Python and convert it to a datetime.datetime with strptime but I want to filter the rows in SQL.

How can I write SQL to do the equivalent of Python's strptime? - e.g. take the concatenated parts of a string and interpret it as a date? (I can figure date->string, but not string->date.)

How can I persuade SQLAlchemy to generate that SQL?

Solution

On sqlite you would need to convert your text. Following might help:

qry = session.query(SomeTable)
dt_column =(func.substr(SomeTable.date, 7) + "-" +
            func.substr(SomeTable.date, 4, 2) + "-" +
            func.substr(SomeTable.date, 1, 2) + " " + SomeTable.time)
dt_column = func.datetime(dt_column)
qry = qry.filter(dt_column <= datetime(2013, 1, 1, 23, 59, 59))

Even better would be to use sqlalchemy Hybrid Attributes, so that you can get the datetime field both on the python level and on the database:

class SomeTable(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    date = Column(String)
    time = Column(String)

    @hybrid_property
    def datetime(self):
        # @todo: add python parsing of date and time to produce the result
        str_value = self.date + self.time
        return datetime.strptime(str_value, "%d/%m/%Y%H:%M:%S")

    @datetime.expression
    def datetime(cls):
        # @note: query specific value
        dt_column =(func.substr(cls.date, 7) + "-" +
                    func.substr(cls.date, 4, 2) + "-" +
                    func.substr(cls.date, 1, 2) + " " + cls.time)
        dt_column = func.datetime(dt_column)
        return dt_column

...
qry = session.query(SomeTable)
qry = qry.filter(SomeTable.datetime <= datetime(2013, 1, 1, 23, 59, 59))

Note that on client you will have datetime instance, while on sqlite level it is still going to be a string.

Comments

[jordanm]

To do this an actual query, your database software is going to be relevant since it will require string parsing functions in the SQL.

[Oddthinking]

Thanks. That's awesome. I need to read up about Hybrid Attributes. They sound very useful.

[Binit Amin]

Hi Did you found any solution? I am having the same problem. I am using the SQLAlchemy and date time column in my source table is in varchar with different different format. I want load it into to target table with Date format in date column.