0

I have this bit of code that is not working...

currentTime = dt.utcnow().date() get just the day has to be UTC cause that what stored in db.
print(currentTime)  #shows day of 2020-09-03
cur = conn.cursor()
cur.execute("SELECT f.user_id, u.first_name, f.location, f.checkin, f.checkout FROM auth_user u JOIN covidlog_onsitelog as f ON (f.user_id=u.id) WHERE CAST(f.checkin as 'DATE()')  = ? or CAST(f.checkout as 'DATE()') =?", (currentTime,currentTime))

The field was created as a model in django and has in it items like:

2020-09-03 00:40:58.901602

But I just want to compare the DAY regardless of the time...though it is not working it seems.

So to me this looks right but does not yield the results so I can only assume it is still somehow comparing the date with the time stamp too... any idea how NOT to do this and literally compare against the date?

GMB
  • 216,147
  • 25
  • 84
  • 135
Codejoy
  • 3,722
  • 13
  • 59
  • 99

2 Answers2

1

Do you want date()?

where date(f.checkin) = date(?) or date(f.checkout = date(?)

Although a bit longer to type, this might be more efficiently expressed without using date functions on the table columns:

where 
    (f.checkin >= date(?) and f.checkin < date(?, '+1 day')
    or (f.checkout >= date(?) and f.checkout < date(?, '+1 day')
GMB
  • 216,147
  • 25
  • 84
  • 135
1

Check if your f.checkin and f checkout fields are datetimestring in which case you may use date(f.checkin) and date(f.checkout) instead of CAST(f.checkin As 'Date()') You may want to have a look at "https://stackoverflow.com/questions/4428795/sqlite-convert-string-to-date" and to: "https://www.tutlane.com/tutorial/sqlite/sqlite-date-function#:~:text=Syntax%20of%20SQLite%20date(),datetime%20string%20value%20into%20date.&text=The%20SQLite%20date()%20function,YYYY%2DMM%2DDD%20format." Your WHERE clause will look like:

WHERE
    DATE(f.checkin) = ? OR DATE(f.checkout) = ?
Erick
  • 301
  • 3
  • 12