Issue with comparing dates with Room persistence library

[Damon Getsman]

Hello everybody.

I have another question regarding Room persistence library. I've been working with it successfully for awhile now, but due to the inability of SQLite to work with a LocalDateTime natively, I'm having some issues now.

Not sure if this is directly relevant, due to the way that I'm handling things, but in my app, I'm saving a timestamp for the following object using the following type converters:

@Entity
public class Usage {
    @PrimaryKey(autoGenerate = true)
    private int             id;
    @ColumnInfo(name="sub_id")
    private int             sub_id;
    @ColumnInfo(name="dosage")
    private float           dosage;
    @ColumnInfo(name="timestamp")
    private LocalDateTime   timestamp;
    @ColumnInfo(name="notes")
    private String          notes;
}

(type converters follow):

    @TypeConverter
    public static LocalDateTime toLocalDateTime(long value) {
        return LocalDateTime.ofEpochSecond(value, 0, ZoneOffset.UTC);
    }

    @TypeConverter
    public static long fromLocalDateTime(LocalDateTime ldt) {
        return ldt.toEpochSecond(ZoneOffset.UTC);
    }

In my app, I'm allowing the user to select a date via a Calendar (setting the hour, minute, second, and lower values all to 0) and then I'm trying to utilize a query to select all records prior to the date selected. I then utilize the Calendar.getTimeInMillis() method to attempt to convert this to a value that will be comparable to the epoch second value that is saved in the database with the following code:

public void purgeDatabase(View v) {
    if (validateData()) {
        //now let's get down to the nitty gritty and delete the entries
        GlobalMisc.showSimpleDialog(this, "Wiping entries",
                "Purging the database's records on the selected substance prior to the date " +
                "that you have selected.");
    } else {
        GlobalMisc.showSimpleDialog(this, "Not purging anything",
                "Not purging any administrations at this time.");
    }

    int countToFry = Permanence.getUsagePriorToCount(tmpCal.getTimeInMillis());
    GlobalMisc.showSimpleDialog(this, "Toast Count",
            "This would toast " + countToFry + " records from the database.");
}

Referenced code in Permanence is as follows:

public class Permanence {
    private static AppDatabase sDb;

    public static int getUsagePriorToCount(long ts) {
        return sDb.getUsageDao().getPriorToDateUsageCount(ts);
    }
}

Referenced code via sDb.getUsageDao() is as follows:

@Query("SELECT COUNT(*) FROM Usage WHERE timestamp > :ts")
int getPriorToDateUsageCount(long ts);

The behavior that I am seeing is that when my timestamp > :ts comparison is utilizing greater than (as here), I am getting a count of 0 entries via the query. When this is reversed I'm getting a count for all of the entries. So I'm thinking that either the long values that I'm trying to compare are not equivalent (epoch milliseconds is what I was shooting for w/Calendar.getTimeInMillis() and LocalDateTime.toEpochSecond()), or, well, something else that I don't have a clue about due to the fact that I'm not at any level of serious proficiency with Room or SQLite queries in general at this point.

For brevity's sake, I have omitted portions of the classes referenced above, due to the fact that the boilerplate code is working fine with other methods in the same classes. I apologize if this is a faux pas, and will be more than happy to post more complete examples of the applicable code snippets. Along the same lines, I'm not really sure what else as far as code examples may be applicable to this problem; I think I've hit everything, but if I'm missing something else please let me know and I would be more than happy to post whatever hasn't been properly included. My apologies in advance if anything of the sort has been missed.

I'd like to thank in advance anybody who reads this and especially those who are willing to help me troubleshoot a little bit farther in this process. I'm really not sure where to go with it at this point and I'm very grateful for anything that you may have to offer.

 

[23tony]

I have recently learned that SQLite treats all integer types the same (internally, there isn't a LONG vs INT), but I don't know about Room.

I assume you're not getting any errors. I would suggest adding a bunch of logging in your code to trace the values through the process and see where the problem occurs - at least that way you can narrow down your focus.

 

[Damon Getsman]

I ended up starting to log all of the variables in question and ended up determining that my error was somewhere else completely. Oops. Didn't really even think about logging things before, I didn't mean to be lazy about it. Guess I'd had a long break from coding and that probably had my skills down a bit for the count.

Anyway, thank you for the advice, it ended up leading me to getting things working again, in a roundabout kind of way.