0

I have events stored in a roomDB with starttime and endtime for each event stored as timestamps. I now need to return events for a given month and year and hence I built the following query but it is returning 0 results... any thoughts?

SELECT * FROM events WHERE strftime('%m',datetime(startDate/1000, 'unixepoch')) = 11 AND strftime('%Y',datetime(startDate/1000, 'unixepoch')) = 2021 OR strftime('%m',datetime(endDate/1000, 'unixepoch')) = 11 AND strftime('%Y',datetime(endDate/1000, 'unixepoch')) = 2021 ORDER BY eventStart ASC

I added single quotes around 11 and 2021 and it gives me results in app inspection, but when I address it as values passed in the function instead of 11 and 2021 but as :newMonth and :newYear - the query doesnt return any results.

madhall
  • 162
  • 1
  • 13
  • 1
    You could add your calculations time calculations to your select statement to see what is being found before filtering with a where clause. Perhaps this syntax would be helpful as well: https://stackoverflow.com/questions/650480/get-month-from-datetime-in-sqlite – Dean Nov 12 '21 at 14:25
  • I added single quotes around 11 and 2021 and it gives me results in app inspection, but when I address it as values passed in the function instead of 11 and 2021 but as :newMonth and :newYear - the query doesnt return any results.. – madhall Nov 12 '21 at 14:49

0 Answers0