Android - SQLite - SELECT BETWEEN Date1 AND Date2

Question

Mac OS-X

Android

Eclipse with ADT

SQLite

I'm new to creating Android Apps and Ive researched this heavily but I'm getting nowhere. I need to query my SQLite database to return all the rows between 2 dates. What I have learnt from my research so far is that there is no DateTime column in Androids SQLite database and I have to save it as a text column. But I think the problem lies with trying to compare Strings but I can't figure out a way around it. Here is my code :

DB = currentContext.openOrCreateDatabase(DBName, 0, null);
DB.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, Date VARCHAR(40), Hours INT(3));");

I am not getting any errors but I am not returning any results from my RawQuery. Here is my code:

Cursor c = newDB.rawQuery("select ID, Date, Hours from " + tableName + " where Date BETWEEN '" + date1 + " 00:00:00' AND '" + date2 + " 99:99:99'", null);

if (c != null ) {
  Log.d(TAG, "date1: "+date1);
  Log.d(TAG, "date2: "+date2);
  if  (c.moveToFirst()) {
     do {
        int id = c.getInt(c.getColumnIndex("ID"));
        String date1 = c.getString(c.getColumnIndex("Date"));
        int hours1 = c.getInt(c.getColumnIndex("Hours"));
        results.add(+ id + "    Date: " + date1 + "    Hours: " + hours1);
     }
         while (c.moveToNext());
      }
}

I have also tried the following statement but they do not yield results either:

Cursor c = newDB.rawQuery("select ID, Date, Hours from " + tableName + " where Date BETWEEN " + Date(date1) + " AND " + Date(date2) + "", null);

I got this statement from other StackOverflow answers but I cannot find any documentation on the date() method. Im not sure whether it is obsolete now but I get error saying "Date(String) method is undefined for the type (classname)" and I have tried importing some JAVA Libraries.

Does anyone know the correct way to create a rawData() query that will get the rows between selected dates??

Further info, these are my INSERT statements and the date format that goes into the database:

newDB.execSQL("INSERT INTO " + tableName + " (Date, Hours) Values ('" + ph_date + "'," + hours + ");");
String date1 = "12/1/13"
String date2 = "25/1/13"

The inventor of SQLite [explained](http://use-the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-conference) that it is indeed meant for *light* purposes, intended more as a replacement for plain files than as a serious database. For more serious work consider the pure-Java [H2 Database Engine](http://h2database.com/html/main.html), with Android as a supported platform discussed [here](http://stackoverflow.com/q/3346236/642706). — Basil Bourque, Sep 05 '15 at 20:21

score 7 · Accepted Answer · answered Jan 11 '13 at 01:27

Ok, so I could not get string dates to work, so I had to convert String Dates to Calendar Dates to Unix Time before adding them to the SQLite database and convert them back (Unix Time to Calendar Dates to String) when displaying them. Unix Time allows calculations (order by, sort ascending, between etc) done on the date columns and it is the best method to use after long hours of trial and error. Here is the code I ended up using:

Cursor c = newDB.rawQuery("select ID, Date, Hours from " + tableName + " where Date BETWEEN '" + startDateQueryDate + "' AND '" + endDateQueryDate + "' ORDER BY Date ASC", null);

            if (c != null ) {
                if  (c.moveToFirst()) {
                    do {
                        int tempId = c.getInt(c.getColumnIndex("ID"));
                        long tempUnixTime = c.getLong(c.getColumnIndex("Date"));

                        //convert tempUnixTime to Date
                        java.util.Date startDateDate = new java.util.Date(tempUnixTime);

                        //create SimpleDateFormat formatter
                        SimpleDateFormat formatter1;
                        formatter1 = new SimpleDateFormat("dd/MM/yyyy", Locale.UK);

                        //convert Date to SimpleDateFormat and convert to String
                        String tempStringStartDate = formatter1.format(startDateDate);

                        int tempHours = c.getInt(c.getColumnIndex("Hours"));
                        results.add(+ tempId + "    Date: " + tempStringStartDate + "    Hours: " + tempHours);
                    }while (c.moveToNext());
                }
            }

CL. · Answer 2 · 2013-01-09T08:39:28.967

6

You must store date values in a format understood by SQLite. For plain dates, this would be a YYYY-MM-DD string, or a seconds value, or a Julian date number.

To format a date as string, use something like this:

Date date1 = ...;
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
String dateAsString = df.format(date1);

To format a date as seconds value, divide the return value of Date.getTime() by 1000.

If your date variables already are strings, you must ensure that they have the correct format.

If your date strings do not have the yyyy-MM-dd format, SQLite's date functions will not understand them, and comparisons will not work (because the string must begin with the most significant field, the year, and all fields must have a fixed length, so that string comparisons come out correct).

edited Jan 09 '13 at 08:39

answered Jan 08 '13 at 09:17

CL.

173,858
17
217
259

So you are saying I CAN compare Strings BUT they must be put into the SQLite database as a DateFormat String using the given method you mentioned? This way I can still use my same rawQuery statement? – Phil Hunter Jan 08 '13 at 17:37
I get error: 01-08 21:34:28.120: E/AndroidRuntime(26265): FATAL EXCEPTION: main 01-08 21:34:28.120: E/AndroidRuntime(26265): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.tescoapp2/com.example.tescoapp2.DataListView}: java.lang.IllegalArgumentException 01-08 21:34:28.120: E/AndroidRuntime(26265): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2243) 01-08 21:34:28.120: E/AndroidRuntime(26265): Caused by: java.lang.IllegalArgumentException 01-08 21:34:28.120: E/AndroidRuntime(26265): at java.text.DateFormat.format(DateFormat.java:365) – Phil Hunter Jan 08 '13 at 21:45
From SQLite's point of view, date strings are just strings. However, strings must be in that format to be understood by SQLite's built-in date functions, and to compare two date strings correctly, the most-significant field, the year, must come first. – CL. Jan 08 '13 at 22:12
I assume `date1` is of type `Date`. If it is `Calendar`, use `date1.getTime()`. – CL. Jan 08 '13 at 22:13
date1 is just of type String. – Phil Hunter Jan 09 '13 at 01:24
Wow! It worked with charm! I didn't expecct it after trying lots of different approach, but using date in `yyyy-MM-dd` actually works. Saved my day! – Dev4Life Sep 08 '21 at 16:41

score 4 · Answer 3 · answered Sep 05 '15 at 06:00

getting top two name of for 2 days

Calendar cal = Calendar.getInstance();
            Date now = new Date();
            cal.setTime(now);
            String currentDate = sdf.format(cal.getTimeInMillis());
            cal.add(Calendar.DAY_OF_YEAR, -1);
            String previusdDate = sdf.format(cal.getTimeInMillis());

            Log.e("Start date", currentDate);
            Log.e("End date", previusdDate);
            SQLiteOpenHelper helper;
            SQLiteDatabase db;
            helper = new My_SQliteHelper(mContext, "MyDB", null, 1);
            db = helper.getWritableDatabase();

            Cursor cr = db.rawQuery("SELECT name,sum(minutes),date FROM Historyinfo WHERE date BETWEEN '" + previusdDate + "' AND '" + currentDate + "' GROUP BY name ORDER BY SUM(minutes) DESC LIMIT 2", null);

100% working

score -1 · Answer 4 · answered Jan 08 '13 at 05:25

Not sure where you read that Date / Time should be stored as strings !!! I dis-agree.

I prefer storing Date / Times as INTEGER values. Read: http://www.sqlite.org/datatype3.html#datetime

When Inserting data, convert your Date / time as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. For e.g. 1st Jan 2012 00:00:00 GMT is represented as 1356998400000

The Android Date / Calendar classes have built in functions to convert dates in UNIX Times and vice versa.

Check : http://developer.android.com/reference/java/util/Date.html#getTime%28%29

When selecting, you can use your normal select statements like BETWEEN or anything you prefer.

I have been using this for many years and it works like a charm :D

Says [here](http://www.sqlite.org/datatype3.html#datetime) it should be stored as text or integer data format. — Phil Hunter, Jan 08 '13 at 17:40

Android - SQLite - SELECT BETWEEN Date1 AND Date2

4 Answers4

Linked