Selecting previous dates from SQLite correctly-CodePudding

After reading a dozen replies here and testing them, I have no choice but to ask for help.

My goal is quite simple, I wish to select data from:

The table I have uses a column of type TEXT with the name "timestamp", the format of time being save is: 2022-03-04 05:44:11.

Query should be quite simple but from what I noticed there are many functions unsuported like DATE_SUB, so I have tested what I found around here.

For example:

SELECT `timestamp` FROM ... where datetime(timestamp) >= datetime('now', '-1 Day')

Result:

2022-03-03 16:33:35
2022-03-03 15:05:17
2022-03-04 05:44:11
2022-03-04 05:40:22
2022-03-04 05:36:38
2022-03-04 05:25:53
2022-03-04 05:16:16

This is incorrect, because it will return previous day data today inclusive. I don't want that, I need the previous day only.

Same thing happens with -1 Hour and everything else. I have also tried with strftime but without success.

This task should be extremely simple to achieve and yet SQLite does not "play ball".

Can someone advise how to do this please?

Many Thanks

CodePudding user response：

For the rows of the previous day use the function date() to strip off the time part of the timestamp:

WHERE date(timestamp) = date('now', '-1 day')

For the rows of the previous hour use the function strftime() with modifiers that strip off minutes and seconds:

WHERE strftime('%Y-%m-%d %H', timestamp) = strftime('%Y-%m-%d %H', 'now', '-1 hour')