Android Room - retrive rows for 30 days

Question

In my app I capture the date/time in the Date format yyyy/MM/dd HH:mm:ss. I then use the following type converts to store the date as a long in the db.

  @TypeConverter
public static Long toDate(Date date){
    return date == null ? null : date.getTime();
}

@TypeConverter
public static Date toLong(Long timestamp){
    return timestamp == null ? null : new Date(timestamp);
}

The date is stored in the db as: 1591353144826

If I run a query like: SELECT * FROM exampleTable it will return all the rows with no problem.

If I run SELECT * FROM exampleTable WHERE workoutDate >= datetime('now', '-30 day') No rows will return. My objective is to be able to filter rows by week, month, quarter, year etc. But the WHERE clause returns 0 rows. I have to remove the WHERE clause to return rows.

Could someone help me root cause this issue? I thought storing the date as long using typeconverters was the correct way to be able to use the datetime search functions.

Answer 1

Your workoutDate is stored as milliseconds since epoch. SQLite function strftime() can be used to get the seconds since epoch for a desired date in the past. Multiply by 1000 to get milliseconds:

SELECT * FROM exampleTable
     WHERE workoutDate >= (1000 * strftime('%s', datetime('now', '-30 day')))