Search code examples
androidsqlitestrftime

Filtering SQLite records using strftime

I have SQLite database in Android app with records like sample below

id           date
1            2023-01-29T15:56:00.733533+01:00
2            2023-01-29T15:56:02.092214+01:00

I need to filter these records by days, month, etc. I found that strftime function should the best way to do this, but my SELECT is not working. Can someone please help what I'm doing wrong.

My SELECT is

SELECT * FROM history WHERE date = strftime('%Y-%m-%d', '2023-01-29');

Thank you.

Solution

  • The time is stored in ISO-8601 standard format

    Do this instead

    
SELECT * FROM history WHERE strftime('%Y-%m-%d', date)='2023-01-29'

//for a particular month

SELECT * FROM history WHERE strftime('%m', date)='2'