How to compare two dates using Android Room?

Question

Summary

I'm making an Android app, and I have a Room entity called Objective which, among other things, stores its startDate and endDate.
I would like to make a query in which I can retrieve the "active" objectives, or in other words, the objectives which have already started, but haven't yet ended.

This is what I've got so far:

Query

@Dao
public interface ObjectiveDao {
    /* some code goes here... */

    /*
       For an objective to be active, its start date should be before the current day,
       and its end date should be after the current day.
    */
    @Query("SELECT * FROM objectives " +
            "WHERE end_date >= strftime('%s', 'now') + 0 " +
            "AND start_date <= strftime('%s', 'now') + 0")
    List<Objective> getActive();

    /* rest of the code goes here... */
}

Problem

My problem lies in the fact that, when I call this method, an empty list is returned, instead of the expected list of active objectives (at least this has happened in the various tests I've tried to run).

(See the edit at the bottom to see what was wrong with this problem.)

---

Extra information

I will also include my Entity, TypeConverter, RoomDatabase and testing code just in case it can help you with anything.

Entity

@Entity(tableName = "objectives")
public class Objective {
    @PrimaryKey(autoGenerate = true)
    private int id;
    
    /* 
        I feel like I don't need to 
        include the whole thing, just the part that matters.
    */
    
    @ColumnInfo(name = "start_date")
    private LocalDate startDate;

    @ColumnInfo(name = "end_date")
    private LocalDate endDate;

    public Objective() {
        this("", ObjectiveType.OBJECTIVE_DO, 0, LocalDate.now(), LocalDate.now().plusDays(1));
    }

    public Objective(String title, ObjectiveType type, int quantity, LocalDate startDate, LocalDate endDate) {
        this.title = title;
        this.type = type;
        this.quantity = quantity;
        this.startDate = startDate;
        this.endDate = endDate;
        id = 0;
    }

    /* rest of the code goes here...*/
}

TypeConverters

public class Converters {
    @TypeConverter
    public static LocalDate fromTimestamp(Long value) {
        return value == null ? null : LocalDate.ofEpochDay(value);
    }

    @TypeConverter
    public static Long localDateToTimestamp(LocalDate date) {
        return date == null ? null : date.toEpochDay();
    }
    /* rest of the code goes here...*/
}

RoomDatabase

@Database(entities = {Objective.class}, version = 1)
@TypeConverters({Converters.class})
public abstract class AppDatabase extends RoomDatabase {
    public abstract ObjectiveDao getObjectiveDao();
}

Instrumented Test

class TestUtil {
    static Objective createObjective() {
        Objective objective = new Objective();
        objective.setTitle("Test Objective");
        objective.setType(ObjectiveType.OBJECTIVE_DO);
        objective.setQuantity(10);
        objective.setStartDate(LocalDate.now());
        objective.setEndDate(LocalDate.now().plusDays(1));

        return objective;
    }
}

public class ObjectivesInstrumentedTest {
    private ObjectiveDao objectiveDao;
    private AppDatabase db;

    @Before
    public void createDb() {
        Context context = ApplicationProvider.getApplicationContext();
        db = Room.inMemoryDatabaseBuilder(context, AppDatabase.class).build();
        objectiveDao = db.getObjectiveDao();
    }

    @After
    public void closeDb() throws IOException {
        db.close();
    }

    @Test
    public void objectiveIsInsertedIntoDatabase() {
        Objective objective = TestUtil.createObjective();
        /*
            id is set to 1 because, otherwise, Room automatically sets the ID to 1,
            and when the objects are compared they do not match. (a value of 0 counts as null).
        */
        objective.setId(1);
        objectiveDao.insert(objective);
        List<Objective> objectives = objectiveDao.getAll();
        assertThat(objectives.get(0), equalTo(objective));
    }

    @Test
    public void objectiveIsActive() {
        Objective objective = TestUtil.createObjective();
        objective.setId(1);
        objectiveDao.insert(objective);
        List<Objective> objectives = objectiveDao.getActive();
        assertThat(objectives.get(0), equalTo(objective));
    }
}

In case you want to know, the first test runs just fine, but I get an IndexOutOfBoundsException when running the second one.

I hope you can help me solve this problem, and thank you in advance for taking the time to read through this and think about any possible solutions.

Edit

As @MikeT pointed out, I was storing these dates not as seconds, nor as unix timestamps, but as days elapsed since 1970-01-01.

Answer 1

Strftime's %s returns the date time in seconds, ofEpochDay returns the number of days since 1970-01-01. So you need to compare likewise.

I believe that you might be able to use :-

@Query("SELECT * FROM objectives " +
            "WHERE end_date >= CAST(strftime('%J','now') - strftime('%J','1970-01-01') AS INTEGER) " +
            "AND start_date <= CAST(strftime('%J','now') - strftime('%J','1970-01-01') AS INTEGER);")

based upon strftime and LocalDate but I'm unsure if there are discrepancies. I would suggest not storing the data in such an awkward format and storing it in a format that is recognised by SQLite as a datetime (as per the link below)