Storing dates in SQLite and performing search by date

Question

I want to insert dates like "2013-12-24" in an SQLite database. My requirements include date wise search functionality.

query for table -

CREATE TABLE IF NOT EXISTS LEAD_TIME_TABLE
             (
             LEAD_TIME_ID
             INTEGER PRIMARY KEY AUTOINCREMENT,
             **LEAD_TIME_VALUE
             DATE NOT NULL,**
             LEAD_TIME_TYPE
            TEXT NOT NULL);

When I insert data in this table, the date is parsed like from 2013-12-24 to Tue Dec 24 19:00:00 EST 2013

This is my method for parsing dates:

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
dateFormat.setTimeZone(TimeZone.getTimeZone("IST"));
Date date = null;
try {
    date = dateFormat.parse("2013-12-24");
} catch (ParseException e) {
    e.printStackTrace();
}

When i am searching with a query such as:

select * from lead_time where value between 'Thu Dec 26 13:30:00 EST 2013' and 'Tue Dec 31 13:30:00 EST 2013'

In the results I am getting unexpected result. How can i resolve that?

What results are you getting, and what are you expecting instead? — Michael Foukarakis, Jan 02 '14 at 09:48

score 1 · Accepted Answer · edited May 23 '17 at 12:11

Date and DateTime are not supported datatypes in SQLite. As SQLite creators themselves propose we should use string or integer for storing such types. The solution you propose uses TEXT.

However, I think that generally it is wrong decision to use string instead of integer, especially if you want to compare. Here is another thread in SO discussing the same. You can also check the recommended solution there. I also support this approach.

score 0 · Answer 2 · answered Jan 02 '14 at 10:14

For my DB's I use type "LONG" (basically "INT") and store the time in epoch millis. Then you can use an instance of Calendar to set the values of various time intervals needed for your query:

select * from lead_time where value between calendar1.getTimeInMillis() and calendar2.getTimeInMillis();

Seems to work well but the tables don't have dates that are easily readable. Small price for fast queries and easily portable data IMO.

score -2 · Answer 3 · edited May 23 '17 at 11:56

-2

You can use parameterized queries : How do I use prepared statements in SQlite in Android?

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("select * from lead_time where value between ? and ?");
stmt.bindDate(1, date1);
stmt.bindDate(2, date2);
stmt.execute();

edited May 23 '17 at 11:56

Community

1
1

answered Jan 02 '14 at 09:48

Thomas B. Lze

1,070
7
14

Bullshit. No `bindDate` method. `Date` is not a type for SQLite – Boris Strandjev Jan 02 '14 at 09:55

Storing dates in SQLite and performing search by date

3 Answers3