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?

To do this an actual query, your database software is going to be relevant since it will require string parsing functions in the SQL. — jordanm
– jordanm, Commented Jan 15, 2014 at 0:26
@jordanm :-( It is sqlite3, but that was always an interim measure until mysql is required. — Oddthinking
– Oddthinking, Commented Jan 15, 2014 at 0:55

van · Accepted Answer · 2014-01-16 10:30:44Z

6

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.

answered Jan 16, 2014 at 10:30

van

77.5k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oddthinking Over a year ago

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

Binit Amin Over a year ago

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.

Collectives™ on Stack Overflow

Converting string to date timestamp in SQLAlchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related