How to query SQLite database to return all dates/times between 2 specific dates/times which are inserted as milliseconds?

Question

I have an SQLite database with 4 columns. I inserted a date/time values in COLUMN_DATE as milliseconds with the "System.currentTimeMillis()" method and I want to query the SQLite database to get all rows between 2 specific dates also represented as milliseconds. This query must include rows with these 2 specific dates too.

Here is my SQLite database:

public static final String CREATE_SQL = "create table " + TABLE_NAME + "(" + COLUMN_ID + " integer primary key autoincrement, " +
        COLUMN_DATE + " integer not null, " + COLUMN_STATUS + " text not null, " + COLUMN_TOTAL + " text not null);";

And here is the database with inserted values:

I want to query the SQLite database by 1578530800000 (ID=2) as a start date and 1578698202000 (ID=5) as an end date and get all rows between those specific dates including those dates itself. Means I want table marked with a red rectangle as a result.

I tried to query the database but never got what I expected. Here is my query example:

String[] projection = {Wdatabase.COLUMN_ID, Wdatabase.COLUMN_DATE, Wdatabase.COLUMN_STATUS, Wdatabase.COLUMN_TOTAL};
String selection = Wdatabase.COLUMN_DATE + " >= " + startDate + " AND " + Wdatabase.COLUMN_DATE +" <= " + endDate;

Cursor r = Objects.requireNonNull(getActivity()).getContentResolver().query(DbProvider.CONTENT_URI, projection, selection, null, null, null);

Can anyone explain to me what is wrong here?

Here is my cursor with logs:

while (r.moveToNext()) {

        Log.i("Arguments", "CursorId: " + r.getInt(0));
        Log.i("Arguments", "CursorDate: " + r.getLong(1));
        Log.i("Arguments", "CursorStatus: " + r.getString(2));
        Log.i("Arguments", "CursorTotal: " + r.getString(3));

    }

As startDate and endDate I pass dateTime in milliseconds as "1578530800000" — Dezo, Jan 11 '20 at 22:09
How are you using **r**? Edit your question to show and also please explain exactly what *but never got what I expected* means. Perhaps add the line `DatabaseUtils.dumpCursor(r)`, then run then edit your question to show the dump (if one) that is in the log. — MikeT, Jan 11 '20 at 23:09
In startDate and endDate variables I put a dateTime as milliseconds but from TimeSquare Calendar picker and there every day starts at 00:00:00, so I think there is an error in my logic not in code. — Dezo, Jan 11 '20 at 23:55
If I set the date range from my TimeSquare Calendar picker from Thu 9 January 2020 00:00:00 (1578524400000) to Fri 10 January 2020 00:00:00 (1578610800000) then i would get result all rows from id 1 to id 4 but not row with id 5 bcs milisecond value in database is bigger than value from Picker even date is same.(Fri 10 January 2020) — Dezo, Jan 12 '20 at 00:20
If someone has some experience with saving dates as milliseconds in the SQLite database I would like to hear some suggestions if this solution good or is completely wrong. Tnx — Dezo, Jan 12 '20 at 00:31

score 1 · Accepted Answer · answered Jan 12 '20 at 05:27

As per your comment Thu 9 January 2020 00:00:00 (1578524400000) to Fri 10 January 2020 00:00:00

Then you would not expect to get ID 5 as that is Fri 10 January 2020 23:16:42 which is the same date BUT a later time.

So if you want to get all rows from date to date ignoring time then you could use what equates to :-

SELECT COLUMN_ID,COLUMN_DATE,COLUMN_STATUS,COLUMN_TOTAL FROM thetable WHERE date(COLUMN_DATE / 1000,'unixepoch') BETWEEN date(1578530800000 / 1000,'unixepoch') AND  date(1578697200000 / 1000,'unixepoch');

SO

String selection = "date(" +Wdatabase.COLUMN_DATE + "/ 1000,'unixepoch') BETWEEN date(" + startDate + "/1000,'unixepoch') AND "date(" + endDate + "/1000,'unixepoch')";

OR (as would be recommended)

String selection = "date(" +Wdatabase.COLUMN_DATE + "/ 1000,'unixepoch') BETWEEN date(?/1000,'unixepoch') AND date(?/1000,'unixepoch')";
Cursor r = Objects.requireNonNull(getActivity()).getContentResolver().query(DbProvider.CONTENT_URI, projection, selection, new String[]{String.valueOf(startDate),String.valueOf(endDate)}, null, null);

Thus the paremeters are bound (i.e. each ? in the WHERE clause is safely replaced by the values on a 1 per 1 basis)
NOte the use of BEWTEEN this simplifies the query a little as it only needs the column to be specified once.
if you wanted to the minute rather than day the use datetime instead of date (3 occurances). Basically the / 1000 is dropping the millicseconds.
Note the above is in-principle code and has not been run or tested and may therefore contain some errors. Saying that the original SQL has been tested using

:-

DROP TABLE IF EXISTS thetable;
create table thetable (COLUMN_ID integer primary key autoincrement, COLUMN_DATE integer not null, COLUMN_STATUS text not null, COLUMN_TOTAL text not null);
INSERT INTO thetable (COLUMN_DATE,COLUMN_STATUS,COLUMN_TOTAL) VALUES
    (1578524400000,'enter',0),
    (1578530800000,'exit',1),
    (1578611801022,'enter',2),
    (1578697200000,'enter',3),
    (1578698202000,'exit',1),
    (1578783710000,'enter',2);

SELECT COLUMN_ID,COLUMN_DATE,COLUMN_STATUS,COLUMN_TOTAL, datetime(COLUMN_DATE / 1000,'unixepoch') FROM thetable WHERE date(COLUMN_DATE / 1000,'unixepoch') BETWEEN date(1578530800000 / 1000,'unixepoch') AND  date(1578697200000 / 1000,'unixepoch');
DROP TABLE IF EXISTS thetable;

Which results in :-

COLUMN_ID   COLUMN_DATE COLUMN_STATUS   COLUMN_TOTAL    datetime(COLUMN_DATE / 1000,'unixepoch')
2   1578530800000   exit    1   2020-01-09 00:46:40
3   1578611801022   enter   2   2020-01-09 23:16:41
4   1578697200000   enter   3   2020-01-10 23:00:00
5   1578698202000   exit    1   2020-01-10 23:16:42

score -1 · Answer 2 · answered Jan 11 '20 at 22:04

-1

Your query is wrong. You should place variables to query() method instead of first null, not inside the selection argument.

check this

answered Jan 11 '20 at 22:04

Ilia Kuzmin

165
8

True, but not really an answer. – Shawn Jan 12 '20 at 00:13

How to query SQLite database to return all dates/times between 2 specific dates/times which are inserted as milliseconds?

2 Answers2