Filtering SQLite records using strftime-CodePudding

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.

CodePudding user response：

With strftime() you can format your timestamps to a specific format and the correct syntax is this:

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

or, for a specific month like '2023-01':

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

or, for a specific year like '2023':

SELECT * 
FROM history 
WHERE strftime('%Y', date) = '2023';

CodePudding user response：

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'