CODEWITHSUNDEEP
androidsqlitedate
Sebastian Tan
Sebastian Tan

Reputation: 13

Android SQLite Date query issue, cannot compare records between 2 dates

I am facing huge issues with running date-related queries on the local SQLite database, kindly appreciate your help on this!

I am trying to retrieve all events that have its eventdate within a given year and month. To do this, I calculated the first date and last date of that month, then queried where the event date is within this range.

My test data uses: String[] testEventDates = {"2011-12-17", "2012-02-25", "2012-01-10", "2012-01-01", "2011-12-24","2012-02-25"}

But when I run my query below, I get ALL of these entries retrieved, which is wrong. My query is, (SELECT...WHERE) "eventDate >= " + firstDay.

When I add the other limit, " AND "eventDate < " + lastDay, it also works weirdly, the cursor is null instead.

Please help me if you have any clues about this!

// fetch events by month, between first and last days calculated
public Cursor fetchEventsByMonth(int month, int year) {
    String firstDay, lastDay, correctedMonth;

    if (month == 12) {
        lastDay = Integer.toString(year+1) + "-01-01";
    } else{
        if (month+1 < 10) {
            correctedMonth = "0" + Integer.toString(month+1);
            } else correctedMonth = Integer.toString(month+1);
        lastDay = Integer.toString(year) + "-" + correctedMonth
                + "-01";
    }
    if (month < 10) {
        correctedMonth = "0" + Integer.toString(month);
        } else correctedMonth = Integer.toString(month);
    firstDay = Integer.toString(year) + "-" + correctedMonth + "-01";
    firstDay = "2012-02-25";

    Cursor mCursor =
    mDb.query(DATABASE_TABLE_EVENTS, new String[] { KEY_EID, KEY_ENAME,
            KEY_EINTERESTS, KEY_EBENS, KEY_EDATE, KEY_ETIME, KEY_EVHO, KEY_EABBR,
            KEY_ELOCATION, KEY_ETYPE, KEY_ENATURE, KEY_ESTATUS, 
            KEY_ESIGNEDUP, KEY_ECAPACITY}, "eventDate >= " + firstDay
            // + " AND "eventDate < " + lastDay
                    , null, null, null,
            "eventDate");
    if (mCursor != null) mCursor.moveToFirst();

    return mCursor;
}

Upvotes: 1

Views: 5002

Answers (3)

Hasanur
Hasanur

Reputation: 146

try this, here my example is dealing with complete date where you can deal with only month and year

Date filed in the SQLite table will be TEXT and date data should be stored as "2012-12-31 00:12:00" format that comparison dose not give you trouble and if we consider that fields are date_from and date_to and we are storing current date at to_date then we should get the current date as bellow and

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String to_date = sdf.format(new Date());

and the SQL query should be

crs = db.rawQuery(
  "SELECT _id, date_from, date_to, done_or_not, list_name " +
  "FROM list_tbl " +
  "WHERE " +
  "date_from <= Datetime('"+to_date+"') AND date_to >= Datetime('"+to_date+"')", null);

Upvotes: 0

Maverick
Maverick

Reputation: 3053

You have to format the string date using strfttime() function in SQLite.The different formats is mentioned in the following link.

http://www.sqlite.org/lang_datefunc.html

Hope it helps

Upvotes: 0

StErMi
StErMi

Reputation: 5469

Have you tried to reproduce the query directly? (I mean querying the database with some tools like SQLite Manager).

NB: when you are using STRINGS you have ALWAYS to use single quote! I also modified it to use selection and selection args, so you are much security safer and it do the job of inserting the single quote automatically. Try it So your query have to be: 

Cursor mCursor =
    mDb.query(DATABASE_TABLE_EVENTS, new String[] { KEY_EID, KEY_ENAME,
            KEY_EINTERESTS, KEY_EBENS, KEY_EDATE, KEY_ETIME, KEY_EVHO, KEY_EABBR,
            KEY_ELOCATION, KEY_ETYPE, KEY_ENATURE, KEY_ESTATUS, 
            KEY_ESIGNEDUP, KEY_ECAPACITY}, "eventDate >= ? AND eventDate < ?"
                    , new String[] { firstDay, lastDay}, null, null,
            "eventDate");
    if (mCursor != null) mCursor.moveToFirst();

Also a really big advice is to never store dates and times with strings. First of all because they are static in this way, instead you have to think that people live in different countries with different times and timezones so you have to think in a worldwide way :)

The best way to store dates and times is (in my opinion) with a long (time stamp). So when you retrieve a date/time you can pass directly it to a Date constructor or a Calendar constructor. Also it's much, much much easier to compare dates in that way and it's really much faster than String compare :)

Upvotes: 2

Related Questions