Search code examples
javadatabasedatetimeandroid-sqlitelong-integer

how to make selection from my database only by date from long dateInMillis?

I have this method at my fragment

     @Override
public void findTasksByDate(long date) {
    adapter.removeAllItems();


   List<ModelTask> tasks = new ArrayList<>();
    tasks.addAll(activity.dbHelper.query().getTasks(DBHelper.SELECTION_LIKE_DATE + " AND "
                    + DBHelper.SELECTION_STATUS + " OR " + DBHelper.SELECTION_STATUS,
            new String[]{"%"+ date + "%", Integer.toString(ModelTask.STATUS_CURRENT),
                    Integer.toString(ModelTask.STATUS_OVERDUE)}, DBHelper.TASK_DATE_COLUMN));
    for (int i = 0; i < tasks.size(); i++) {
        Log.d("data", "Title = " + tasks.get(i).getTitle());
        addTask(tasks.get(i), false);

    }
}

I need to select all tasks with only date from long date. Do i need to format my date? Is there any simple way to make this?

Solution

  • Do i need to format my date?

    No, you can extract the date from the column(s) and manipulate/manage/manipulate them by using 

    date(substr(the_column,1,10),'unixepoch')

    For Example consider the following (example chosen/selected as real data was available) :-

    SELECT 
    _id, 
    shoplistdateadded, 
    date(substr(shoplistdateadded,1,10),'unixepoch') AS converted_date 
FROM shoplist;
SELECT 
    _id, 
    shoplistdateadded, 
    date(substr(shoplistdateadded,1,10),'unixepoch') AS converted_date 
FROM "shoplist" 
WHERE date(substr(shoplistdateadded,1,10),'unixepoch') 
    BETWEEN date('2019-04-01') 
        AND date('2019-04-30')
;

    The first query results in :-

    enter image description here

    The shoplistadded column is the datetime with milliseconds (obtained via System.currentTimeMillis())

    The converted_date column is the date from the shoplistadded column after dropping the milliseconds and extracting the date (yyyy-MM-DD) by using the SQlite date function. That is, it is passed the first 10 digits (a recognised format) so returns just the date.

    The second query uses this to select only rows for April 2019 i.e the 2019-05-02 row is not returned, the other 3 are.