SQALCHEMY query between two dates

Question

I looked at that link

It's weird because the query im doing is hit and miss. It can't show the dates if the difference is only a few days

SQLAlchemy: how to filter date field?

model:

class UserCallsModel(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    date = db.Column(db.String(90))
    username = db.Column(db.String(90))
    event_name = db.Column(db.String(90))

query:

users = UserCallsModel.query.filter(UserCallsModel.date.between("2016-1-1",  "2016-1-20")).order_by(UserCallsModel.date.desc())

I've got 2 dates that fall within this range but is not getting queried?

Answer 1

I'm not familiar with MySQL, but I imagine it is the same as PG which I've included output below.

When you use the "between" method, you end up using the "BETWEEN" operator, like so...

SELECT * FROM my_table WHERE date BETWEEN '2016-1-1' AND '2016-1-20'

The problem is that the "between" operator does something different for dates versus strings. For example, if the value that it is testing is a string, it will see the arguments (the '2016-1-1' AND '2016-1-20' part) as strings.

mhildreth=# select '2016-1-5' between '2016-1-1' AND '2016-1-10';
 ?column?
----------
 f
(1 row)

Meanwhile, if the value that it is testing is a date object, then it will implicitly convert the strings to date objects, essentially doing the following...

mhildreth=# select '2016-1-5'::date between '2016-1-1'::date AND '2016-1-10'::date;
 ?column?
----------
 t
(1 row)

Thus, my guess is that you want to convert your "date" column to be a date type. If you must leave it a string, then you need to ensure that you are using a date format that also works when doing string comparison. Thus, you'll need 2016-01-01 rather than 2016-1-1.