Viewed   170 times

Mac OS-X


Eclipse with ADT


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);

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"



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());
Sunday, November 20, 2022

You can do something like this:

 mDb.query(MY_TABLE, null, DATE_COL + " BETWEEN ? AND ?", new String[] {
                minDate + " 00:00:00", maxDate + " 23:59:59" }, null, null, null, null);

minDate and maxDate, which are date strings, make up your range. This query will get all rows from MY_TABLE which fall between this range.

Friday, December 9, 2022
SELECT * FROM events 
  WHERE date1<='2012-01-18'
  AND date2>='2012-01-18'
Friday, October 14, 2022

SQLiteConnectionPooling was added in after Android 4.1. Whereas in previous versions a cursor object would simply try to reopen a database if a query or other operation was made on a connection that was closed, with Connection Pooling, an exception is thrown. The NotesDBAdapter is probably a throwback which may have broken compatibility with 4.2.2.

Friday, September 9, 2022

The VACUUM command may be what you want.

This will copy the data to a temporary database and then use this to overwrite the original.

You would use something along the lines of:-


Note the warnings about compatibility in the following:-

SQL As Understood By SQLite - VACUUM

Tuesday, November 15, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :