CODEWITHSUNDEEP
androidsqlitedate
user2769411
user2769411

Reputation: 11

android sqlite date range

I have a sqlite DB in my Android project, and I try to select all rows between dates. But no row is returned by this query:

String where = OperationEntry.COLUMN_NAME_ENTRY_ACCOUNT_FK + " = ? AND " +
           OperationEntry.COLUMN_NAME_ENTRY_DATE + " > date('2013-01-01') AND "+
           OperationEntry.COLUMN_NAME_ENTRY_DATE + " < date('2013-12-31') ;";
String[] whereValue = {accountName};

Cursor c = db.query(
    OperationEntry.TABLE_NAME,  // The table to query
    projection,                 // The columns to return
    where,          // The columns for the WHERE clause 
    whereValue,                 // The values for the WHERE clause
    null,                       // don't group the rows
    null,                       // don't filter by row groups
    sortOrder                   // The sort order
);

Moreover, if I use 2013-01-01 and 2014-12-31, all rows are matching.
I have tested with and without date's function, I have the same issue.
Note that OperationEntry.COLUMN_NAME_ENTRY_DATE is a Date type.

Can you help me? Do you find any mistake in my code?
Thank's
Jonathan.

EDIT: I put here some additional code. query for table creation:

public static final String SQL_CREATE_ENTRIES =
"CREATE TABLE IF NOT EXISTS " + OperationEntry.TABLE_NAME + " (" +
    OperationEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
         ... +
    OperationEntry.COLUMN_NAME_ENTRY_DATE + " DATE, " + ...
" )";

query for insertion:

GregorianCalendar calendar = ...;
String date = calendar.get(Calendar.YEAR) + "-" + calendar.get(Calendar.MONTH) + "-" + calendar.get(Calendar.DAY_OF_MONTH);
ContentValues values = new ContentValues();
values.put(OperationEntry.COLUMN_NAME_ENTRY_TITLE, operation.getTitle());
values.put(OperationEntry.COLUMN_NAME_ENTRY_QTE, operation.getQte());
values.put(OperationEntry.COLUMN_NAME_ENTRY_DATE, date);
    ...

long newRowId;
newRowId = db.insert(
    OperationEntry.TABLE_NAME,
    null,
    values);

Upvotes: 1

Views: 1584

Answers (3)

k3b
k3b

Reputation: 14755

sqlite has no buildin dateformat. the result of you query depends on how the data is stored in the colum ENTRY_DATE.

i am using int values for sqlite-date which are convertet by java code. if your businesslayer wants to search for date-range the databaselayer can search for an int range.

if you execute your query without the where filter, how do the result values look like?

for example i convert from long to string like this

final private static java.text.DateFormat isoDateTimeformatter = new SimpleDateFormat("yyyy-MM-dd'T'h:m:ssZ");

public static String getDateStr(long dateTime) {
    if (dateTime == TimeSlice.NO_TIME_VALUE) {
        return "";
    } else {
        return isoDateTimeformatter.format(new Date(dateTime));
    }
}

to convert from string back to long i use this

public static long parseDate(String mDateSelectedForAdd) {
    try {
        return isoDateTimeformatter.parse(
                mDateSelectedForAdd).getTime();
    } catch (ParseException e) {
        Log.w(Global.LOG_CONTEXT,"cannot reconvert " + mDateSelectedForAdd + " to dateTime using " + isoDateTimeformatter,e);
        return TimeSlice.NO_TIME_VALUE;
    }
}

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

I'm not Android expert but you are obviously misusing date SQLite function. date('2013-01-01') is pointless, it evaluates to '2013-01-01'.

Try instead:

String where = OperationEntry.COLUMN_NAME_ENTRY_ACCOUNT_FK + " = ? AND " +
           "date(" + OperationEntry.COLUMN_NAME_ENTRY_DATE + ") > '2013-01-01' AND " +
           "date(" + OperationEntry.COLUMN_NAME_ENTRY_DATE + ") < '2013-12-31';";

You need to ensure column data returned is properly formated, not the string you hard-coded!

Upvotes: 1

shem
shem

Reputation: 4712

try this:

SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Date firstDate = formatter.parse("2013-01-01");
Date endDate = formatter.parse("2013-12-31");
String where = OperationEntry.COLUMN_NAME_ENTRY_ACCOUNT_FK + " = ? AND " +
OperationEntry.COLUMN_NAME_ENTRY_DATE + " > ? AND "+
OperationEntry.COLUMN_NAME_ENTRY_DATE + " < ?";
String[] whereValue = {accountName, firstDate.getTime(), endDate.getTime()};

Upvotes: 0

Related Questions