I am new sqlite. I have one table that having 3 columns
1. Id
2. FoodName
3. Date and time
(ex: '1' 'Chicken' '2012-08-10 13:54')
I read somewhere Android sqlite doesn't provide date data type.
So I used the text data type for storing the date in local db. it's working fine.
Now I want to write a query for retrieving the record between two dates. I tried but I am not getting it.
==>> code
public class FoodDB {
//Database Related Constants
public static final String KEY_ROWID = "id";
public static final String KEY_NAME = "fooditem";
public static final String KEY_TIMESTAMP = "timestamp";
private static final String DATABASE_NAME = "AJFoodDB";
private static final String DATABASE_TABLE = "AJ_Food";
private static final int DATABASE_VERSION = 1;
Cursor c;
long id;
private static final String DATABASE_CREATE =
"create table "+DATABASE_TABLE+" (id integer primary key autoincrement, "
+ KEY_NAME+" text not null,"
+ KEY_TIMESTAMP+" text not null);";
private final Context context;
private DatabaseHelper DBHelper;
private SQLiteDatabase sqlitedatabase;
public FoodDB(Context ctext){
context = ctext;
}
private static class DatabaseHelper extends SQLiteOpenHelper
{
DatabaseHelper(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public void onCreate(SQLiteDatabase db)
{
db.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSQL("DROP TABLE IF EXISTS titles");
onCreate(db);
}
}
//Insert Row
public void insertSSFiltersDetails(String keyname, String timestamp){
try
{
DBHelper = new DatabaseHelper(context);
sqlitedatabase = DBHelper.getWritableDatabase();
insertRow(keyname, timestamp);
sqlitedatabase.close();
} catch (Exception e) {
System.out.println("Error from inserting data->"+e.toString());
}
}
public long insertRow(String keyname, String timestamp){
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_NAME, keyname);
initialValues.put(KEY_TIMESTAMP, timestamp);
return sqlitedatabase.insert(DATABASE_TABLE, null, initialValues);
}
//Close Data Base
public void closeDBHelper(){
if(DBHelper != null){
DBHelper.close();
}
if (c != null) {
c.close();
c = null;
}
} }
Query and Table
select * from AJ_Food;
1|rice |8/12/2011 16:11
2|Rice |8/13/2012 11:42
3|chicken|8/13/2012 11:42
4|Prawans|8/13/2012 11:43
Best Answer
Even if you have taken text for date in sqlite. You can write the following query to select data between two dates.