Query last day, last week, last month SQLite

Question

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!

Here's a hint. The last day of the month is -1 from the first day of the next month. — JonH, May 08 '12 at 18:23
Thank you @JonH. I have found a solution, but I cannot post it for the time being. In some hours all of you will have it :) — luismiyu, May 08 '12 at 20:01

score 41 · Accepted Answer · edited Apr 01 '15 at 08:30

41

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

edited Apr 01 '15 at 08:30

Kutyel

8,575
3
30
61

answered May 09 '12 at 11:36

luismiyu

701
1
7
10

1

`-6 days` without the space! Can't edit it, it says edit must be at least 6 characters – Anna K. Feb 19 '15 at 20:01
In sqlite 3.9.1 at least, a missing `'localtime'` between `'now', 'start...'` may cause unexpected behavior. They should be `datetime('now', 'localtime', 'start of day')` as an example. – Kevin Reilly Mar 10 '16 at 07:01

score 10 · Answer 2 · answered Jun 28 '16 at 18:02

10

This code should get you the previous month

SELECT * 
FROM statistics 
WHERE date >= date('now','start of month','-1 month')
AND date < date('now','start of month')

answered Jun 28 '16 at 18:02

Derek Larson

103
1
6

score 4 · Answer 3 · edited May 16 '19 at 14:13

4

SELECT * 
FROM statistics 
WHERE date >= date('now','start of month','-1 months')
AND date < date('now','start of month')

On more months, is "months" and not month like as other said before.

edited May 16 '19 at 14:13

Rasel

5,488
3
30
39

answered Sep 17 '18 at 09:14

Victor Radulescu

125
8

score 0 · Answer 4 · edited Aug 08 '12 at 07:08

0

This code will bring previous week records hopefully

SELECT  * FROM  order_master
WHERE strftime('%Y-%m-%d',om_date) >= date('now','-14 days') AND 
strftime('%Y-%m-%d',om_date)<=date('now') order by om_date LIMIT 6

edited Aug 08 '12 at 07:08

Tisho

8,320
6
44
52

answered Aug 08 '12 at 06:55

Issac Balaji

1,441
1
13
25

1

Previous week or previous *two* weeks? – Martijn Pieters Oct 05 '12 at 09:39

score 0 · Answer 5 · answered Apr 29 '13 at 10:34

0

SELECT 
max(date(date, 'weekday 0', '-7 day')) WeekStart,
max(date(date, 'weekday 0', '-1 day')) WeekEnd,date 
FROM table;

answered Apr 29 '13 at 10:34

Kirit Vaghela

12,572
4
76
80

score 0 · Answer 6 · answered Apr 08 '20 at 17:29

You can create a calendar and then get the timestamp from it

final Calendar todayCalendar = Calendar.getInstance();
final long todayTimestamp = todayCalendar.getTime().getTime();
todayCalendar.add(Calendar.DAY_OF_YEAR, -7);
final long aWeekAgoTimestamp = todayCalendar.getTime().getTime();
final String selection = TABLE_COLUMN_DATE_CREATED + " BETWEEN " + aWeekAgoTimestamp + " AND " + todayTimestamp;

Query last day, last week, last month SQLite

6 Answers6

Linked