Zion Zion - 4 years ago 126
SQL Question

SQALCHEMY query between two dates

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?


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))


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 Source

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';
(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;
(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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download