i would need to know the past 7 days record. i want to wrote a query for that in where condition. i have very basic knowledge in sqlite. Please help me for this query.
+1
A:
sqlite doesn't keep track of when your table rows were added. If you need this behavior you should add a timestamp column and set the current time in it when you insert a new row.
MihaiD
2010-04-30 12:26:52
can i use the between clause? i mean select * from tablename order by columnname between currentdate and currentdate-7;
Praveen Chandrasekaran
2010-04-30 12:33:18
Between does not exist in sqlite. You can do SELECT * FROM tablename WHERE columnname > currentdate - 60*60*24*7 ORDER BY columnname. You should look over the sqlite syntax: sqlite.org/lang.html. I am assuming you'll store the date as a UNIX timestamp
MihaiD
2010-04-30 13:07:13
@MihaiD: BETWEEN *does* exist in SQLite.
dan04
2010-05-01 04:46:37
+1
A:
You need to store the date in one of the columns in your DB. In my app I convert the NSDate (iPhone obviously) to a double for storing in the DB using timeIntervalSinceReferenceDate. Given that I can query for a date range.
I wrote functions that calculate the NSDate for midnight X days in the past/future, which I use to determine the startDate and endDate of queries. Here is an excerpt from the code that will give you some idea:
char selectQuery[MAX_STR];
sprintf(selectQuery, "SELECT DateTime FROM Journal WHERE DateTime >= %f AND DateTime < %f;",
[startDate timeIntervalSinceReferenceDate],
[endDate timeIntervalSinceReferenceDate]);
I didn't want to convert NSDate to date time strings because I don't trust the conversions back and forth with all the various timezones and different locale's ways of formatting dates.
progrmr
2010-04-30 12:38:51
this is the actual query for java: select * from table_name where strftime('%Y-%m-%d',field_name)>=date('now','-6 days') and strftime('%Y-%m-%d',field_name)<=date('now') order by field_name desc;
Praveen Chandrasekaran
2010-04-30 14:37:37