CODEWITHSUNDEEP
databasesqlitedatetime
luismiyu
luismiyu

Reputation: 731

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!

Upvotes: 26

Views: 38374

Answers (6)

Jesus Almaral - Hackaprende
Jesus Almaral - Hackaprende

Reputation: 2327

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;

Upvotes: 0

Victor Radulescu
Victor Radulescu

Reputation: 125

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.

Upvotes: 4

Derek Larson
Derek Larson

Reputation: 103

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

Upvotes: 10

luismiyu
luismiyu

Reputation: 731

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

Upvotes: 43

Kirit Vaghela
Kirit Vaghela

Reputation: 12664

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

Upvotes: 0

Issac Balaji
Issac Balaji

Reputation: 1441

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

Upvotes: 0

Related Questions