Java – Android – SQLite – select between date1 and date2

Mac OS-X

Android

Eclipse and ADT

SQLite

I'm new to creating Android apps. I've done a lot of research on it, but I have nowhere to go I need to query my SQLite database to return all rows between 2 dates So far, what I have learned from my research is that there is no datetime column in the Android SQLite database. I have to save it as a text column But I think the problem is trying to compare strings, but I can't find a solution This is my code:

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

I didn't receive any errors, but I didn't return any results from rawquery This 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 also tried the following statements, but they didn't produce results:

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

I got this declaration from other stackoverflow answers, but I couldn't find any documentation about the date () method I'm not sure if it's outdated now, but I get the error that "the date (string) method of type (class name) is undefined". I try to import some Java libraries

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

For more information, these are my insert statement and the date format of entering the database:

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

Solution

OK, so I can't use string dates, so I have to convert string dates to calendar date to UNIX time, then add them to SQLite database and convert them back (UNIX time to calendar date to string) when they are displayed UNIX time allows calculations to be completed on the date column (sorted in order, sorted in ascending order, etc.), which is the best method for long-term trial and error This is my final code:

Cursor c = newDB.rawQuery("select ID,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());
                }
            }