android - sqlite date query

Question

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

Narendra Pal · Accepted Answer · 2012-08-13 06:22:23Z

5

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'

answered Aug 13, 2012 at 6:22

Narendra Pal

6,65413 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

naresh Over a year ago

sqlite> select timestamp from AJ_Food where timestamp between '2012-07-01' and ' 2012-07-07'; select timestamp from AJ_Food where timestamp between '2012-07-01' and '2012-07- 07';

Narendra Pal Over a year ago

@naresh Is this query is helpful to you?

Vaibhav Over a year ago

Yes, no need to change the data from database. Its working properly.

John Woo · Accepted Answer · 2012-08-13 06:29:34Z

1

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

answered Aug 13, 2012 at 6:29

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Ram kiran Pachigolla · Accepted Answer · 2012-08-13 06:29:15Z

0

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'

answered Aug 13, 2012 at 6:29

Ram kiran Pachigolla

21.3k15 gold badges60 silver badges78 bronze badges

1 Comment

naresh Over a year ago

sqlite> select timestamp from AJ_Food where Date < '2011-08-02' and Date >'2012- 08-13'; select timestamp from AJ_Food where Date < '2011-08-02' and Date >'2012-08-13'; SQL error: no such column: Date

Collectives™ on Stack Overflow

android - sqlite date query

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related