Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

database - Query last day, last week, last month SQLite

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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');

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...