CODEWITHSUNDEEP
androidsqlite
naresh
naresh

Reputation: 10392

android - sqlite date query

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

Upvotes: 4

Views: 12944

Answers (3)

John Woo
John Woo

Reputation: 263683

You can convert string to date using date( ) function.

SELECT * 
FROM AJ_Food
WHERE date(dateTimeColumn) BETWEEN date('startDateHere') AND date('endDateHere')

See more Formats HERE

Upvotes: 1

Ram kiran Pachigolla
Ram kiran Pachigolla

Reputation: 21181

select col from Table where Date < '2011-08-02' and Date >'2012-08-13'

or otherwise 

select col from Table where Date between '2011-08-02' and '2012-08-13'

Upvotes: 0

Narendra Pal
Narendra Pal

Reputation: 6604

Even if you have taken text for date in sqlite. You can write the following query to select data between two dates.

select column from Table where columnDate between '2012-07-01' and '2012-07-07'

Upvotes: 5

Related Questions