Flask SQLAlchemy between query return empty list

Question

I just got started using Flask SQLAlchemy and I looked over here to see how I am supposed to do a query between so I found this

But the problem that I got is that it returns the following when executes as below:

SELECT records.id AS records_id, records.created_at AS records_created_at, records.file_type AS records_file_type, records.path_filename AS records_path_filename 
FROM records 
WHERE records.created_at BETWEEN ? AND ?

Code:

today_date = datetime.today().strftime("%d/%m/%Y")
week = (datetime.today() - timedelta(days=7)).strftime("%d/%m/%Y")
month = (datetime.today() - timedelta(days=30)).strftime("%d/%m/%Y")

last_30_days = db.session.query(Records).filter(Records.created_at.between(month, today_date))

And when I add .all() at the end of the last_30_days expression it returns an empty list.

I would also like to mention that when I use just the same expression to query the week, today_date it gets all the results.

This is how the table looks like:

class Records(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created_at = (db.Column(db.String(20), nullable=False))
    file_type = (db.Column(db.String(100), nullable=False))
    path_filename = db.Column(db.String(120), unique=True, nullable=False)

Could anybody explain to me please what's going on? Thanks!

Because you're doing string comparison, not datetime comparison. You need your time in ISO format if you want < or > comparisons on strings to make sense. "YYYY-MM-DD", for example, would allow string comparisons to work. Also note that there is a DateTime and Date format in SQLA — roganjosh
– roganjosh, Commented Oct 21, 2019 at 15:32

roganjosh · Accepted Answer · 2019-10-21 15:43:32Z

2

Your example does not use the Date or DateTime column datatypes that come with SQLAlchemy. You're relying on lexicographic ordering of strings, but for that to work, the date/time stamps require storage along the lines of ISO 8601 e.g. YYYY-MM-DD.

Either change the string format you're using, or select the appropriate column type instead of just String.

answered Oct 21, 2019 at 15:43

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

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

5 Comments

Terchila Marian Over a year ago

All right so I change the format to ISO 8601 and now when I query just the same the db is still giving me the wrong output, any clue why?

roganjosh Over a year ago

How did you migrate the format of the existing data in the DB?

Terchila Marian Over a year ago

I created a new database with just 4 records for testing purposes. the dates are the following 2019-10-15, 2019-10-16, 2019-09-15, 2019-09-20 and the query result of last_30_days is [Records('1', '2019-10-15', 'picture', 'dasdas'), Records('2', '2019-10-16', 'picture', 'dasdasgas')]

roganjosh Over a year ago

Ok, and today is the 21st October, so what's wrong with the results?. Neither of September's values are within the last 30 days....

Terchila Marian Over a year ago

ohh, I know that this would sound silly but I tough is 20. nevermind, thanks a loot buddy!

Collectives™ on Stack Overflow

Flask SQLAlchemy between query return empty list

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related