CODEWITHSUNDEEP
javaandroidsqlitedatetime
oikonomopo
oikonomopo

Reputation: 4065

How to find last / 7 /30 days records of sqlite db when datetime is stored as text?

I have an activity where i save some info to my database. I have two textviews one for date (yyyy-MM-dd) and one for time (HH:mm). if i save datetime as a TEXT, i can sort then desc properly,but CAN i find with sqlite query last/7/30 days records?An example of this query when are the datetime TEXT?

Upvotes: 0

Views: 2067

Answers (1)

slkorolev
slkorolev

Reputation: 6001

First you should calculate the date range you want to analize, then convert its boundaries into the text format (you can use some date formatting ustilities). Then you should query the Db with converted dates as parameters.

Suppose you want last 30 days list:

Calendar theEnd = Calendar.getInstance();
Calendar theStart = (Calendar) theEnd.clone();

theStart.add(Calendar.DAY_OF_MONTH, -30);

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
String start = dateFormat.format(theStart.getTime());
String end = dateFormat.format(theEnd.getTime());

// Now you have date boundaries in TEXT format

Cursor cursor = db.rawQuery("SELECT * FROM table WHERE timestamp BETWEEN "+start+" AND "+end);

Upvotes: 2

Related Questions