0

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!

Terchila Marian
  • 2,225
  • 3
  • 25
  • 48
  • 2
    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 Oct 21 '19 at 15:32

1 Answers1

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.

roganjosh
  • 12,594
  • 4
  • 29
  • 46
  • 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? – Terchila Marian Oct 21 '19 at 15:58
  • How did you migrate the format of the existing data in the DB? – roganjosh Oct 21 '19 at 16:00
  • 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')] – Terchila Marian Oct 21 '19 at 16:01
  • 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.... – roganjosh Oct 21 '19 at 16:03
  • ohh, I know that this would sound silly but I tough is 20. nevermind, thanks a loot buddy! – Terchila Marian Oct 21 '19 at 16:04