Android SQLite Date Query

Question

I have gone through countless posts but still cannot quite seem to achieve the desired result.

My date / timestamps are stored as Strings in the SQLite database where one would look like: Sun Apr 16 00:26:33 GMT+01:00 2017

What I am trying to achieve is to only retrieve items stored in the database from the last 48 hours (now until 2 days ago). The below code, that I have put together, continues to return results older than 48 hours / 2 days.

public Cursor searchInboxEmails(String search, String twoDaysAgo, String today) throws SQLException {

    Cursor cursor = null;

    try {
        if (search != null && !search.trim().isEmpty()) {

            String received = "Received";
            search = "%" + search + "%";

            String query = "SELECT * FROM " + TABLE_NAME_1
                    + " WHERE " + MESSAGE_TYPE + " IN (?)"
                    + " AND " + FROM_EMAIL + " LIKE (?) OR " + CC_EMAIL + " LIKE (?) OR "
                    + SUBJECT + " LIKE (?) OR " + MESSAGE + " LIKE (?)"
                    + " AND " + "DATETIME(" + DATE_RECEIVED + ") BETWEEN DATETIME(?) AND DATETIME(?)"
                    + " ORDER BY " + _ID + " DESC";

            String[] params = {received, search, search, search, search, twoDaysAgo, today};
            cursor = database.rawQuery(query, params);

            if (cursor != null) {
                cursor.moveToFirst();
            }
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
    return cursor;
}

The values 'twoDaysAgo' and 'today' are also formatted Strings which look the same as the stored Strings ('DATE_RECEIVED'). Any help on getting the desired functionality to work would be a great help.

EDIT: I have added an additional column 'TIMESTAMP' which saves the Epoch value (e.g. 1492337028) and the below function works great (just shows the items after the timestamp):

String received = "Received";
        String query = "SELECT * FROM " + TABLE_NAME_1
                + " WHERE " + MESSAGE_TYPE + " IN (?)"
                + " AND DATETIME(" + TIMESTAMP + ") >= DATETIME(?)"
                + " ORDER BY " + TIMESTAMP + " DESC";

        String[] params = {received, twoDaysAgo};
        cursor = database.rawQuery(query, params);

However, when attempting to search for items after the timestamp, it returns items before the timestamp as well so how can I modify the below function to only search for items after the timestamp?

String received = "Received";
            search = "%" + search + "%";

            String query = "SELECT * FROM " + TABLE_NAME_1
                    + " WHERE " + MESSAGE_TYPE + " IN (?)"
                    + " AND " + FROM_EMAIL + " LIKE (?) OR " + CC_EMAIL + " LIKE (?) OR "
                    + SUBJECT + " LIKE (?) OR " + MESSAGE + " LIKE (?)"
                    + " AND DATETIME(" + TIMESTAMP + ") >= DATETIME(?)"
                    + " ORDER BY " + TIMESTAMP + " DESC";

            String[] params = {received, search, search, search, search, twoDaysAgo};
            cursor = database.rawQuery(query, params);

Don't use DATETIME() in your edited solution, and then check whether it works or not. — Qandeel Abbassi, Apr 19 '17 at 15:15

score 0 · Answer 1 · edited May 23 '17 at 11:54

0

You have two options to do what you asked for.

First Option: Change data type of DATE_RECEIVED to INTEGER and then initialize it with time in millis. Do the same for twoDaysAgo and today in params; convert them to time in millis. Now your query should work.

Second option: Check this question. You might need to change your timestamp format for this, I am not sure though.

edited May 23 '17 at 11:54

Community

1
1

answered Apr 19 '17 at 01:51

Qandeel Abbassi

999
7
31

I have added an additional column 'TIMESTAMP' as an INTEGER - please see my updated question. – Toby Clench Apr 19 '17 at 10:31

Android SQLite Date Query

1 Answers1