How to compare timestamp (long milisecond ) value to sqlite database in android

Question

I have a table in SQLite database is called message the message table contains message_date column. I need the data that was inserted 7 days ago. I can't compare the Date because the message_date is stored as a millisecond long value my writen query was :

 Cursor cursor = db.rawQuery("SELECT csId FROM table_messages WHERE date < date('now','-7 days') ", null);

anyone can correction my query

Answer 1

You can get millisecond value from Date().getTime() or System.currentTimeMillis() both will return "the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC."

To get seven days previous timestamp. try this -

Calendar current = Calendar.getInstance();
current.add(Calendar.DATE, -7);
long millis = current.getTime().getTime();//Change this line

Cursor cursor = db.rawQuery(
    "SELECT csId FROM table_messages WHERE date < " + String.valueOf(millis), null);  

EDIT : You see second line of code there you can add subtract days as you wish and extract milliseconds from calender object and use it in your query

EDIT 2 :
1. current.add(Calendar.DATE, -7); Here -7 means you are subtracting 7 days from today. You can make it -30(Subtract 30 days) or just 30(add 30 days) means try changing value because may be table you r querying doesn't have any records matching the condition.

2.Select all rows from table and print it on logcat and print milliseconds you got from Calendar, manually match that milliseconds you got from calendar exist in table or not.

3.Replace this line long millis = current.getTimeInMillis(); with long millis = current.getTime().getTime();

4.If you are getting an error, post error message

If this solves your problem then please mark this post as answer.

Answer 2

You can query with the timestamp > 6 days ago and < 7 days ago

Calendar sixDaysAgo = Calendar.getInstance();
        sixDaysAgo.add(Calendar.DATE, -6);
        long millis6Days = sixDaysAgo.getTimeInMillis();

        Calendar sevenDaysAgo = Calendar.getInstance();
        sevenDaysAgo.add(Calendar.DATE, -7);
        long millis7Days = sevenDaysAgo.getTimeInMillis();

        Cursor cursor = db.rawQuery(
                "SELECT csId FROM table_messages WHERE date > " + String.valueOf(millis6Days) + " AND date < " + String.valueOf(millis7Days), null);