Query last day, last week, last month SQLite
database, datetime, sqlite
Solution
I have found this solution. I hope it works for you.
For last day:
SELECT * FROM statistics WHERE date BETWEEN datetime('now', 'start of day') AND datetime('now', 'localtime');
For last week:
SELECT * FROM statistics WHERE date BETWEEN datetime('now', '-6 days') AND datetime('now', 'localtime');
For last month:
SELECT * FROM statistics WHERE date BETWEEN datetime('now', 'start of month') AND datetime('now', 'localtime');
Problem
I have this table in my Android SQLite DB: ``` CREATE TABLE statistics (subject TEXT, hits INTEGER, fails INTEGER, date DATE) ``` On `date` field is stored `datetime('now', 'localtime')` in every register. Now I must query last day, last week and last month registers for showing some statistics. I've been trying something like this ``` SELECT Timestamp, datetime('now', '-1 week') FROM statistics WHERE TimeStamp < datetime('now', '-1 week') ``` and this ``` SELECT * FROM statistics WHERE date BETWEEN datetime('now', localtime') AND datetime ( 'now', '-1 month') ``` and doesn't work :( How can I do it? Can I check if the query is OK by simply forwarding date in the virtual device emulator? Thanks!