Timestamp Difference Between Java And Sqlite

Hello I have and SLQLite database in which I have table water_logs CREATE TABLE water_logs( _id INTEGER PRIMARY KEY AUTOINCREMENT, amount REAL NOT NULL, icon INTEGER NOT NULL, dat

Solution 1:

Seems to me that you are using 2 different value types.

When you use

Calendarcal= Calendar.getInstance(); 
longtime= cal.getTimeInMillis();

The output value is in Milliseconds, as described here.

While when you use

strftime('%s','now')

The output value is in Seconds, as described here.

So, that might be the cause for the mismatch between the two values. Of course that the value in seconds might undergo some rounding which might change its value a little.

Solution 2:

I will try to provide you the best way to store Dates in SQLite database.

1) Always use integers to store the dates.

2) Use this utility method to store the dates into the database,

publicstaticLongsaveDate(Date date) {
    if (date != null) {
        return date.getTime();
    }
    returnnull;
}

Like,

ContentValuesvalues=newContentValues();
values.put(COLUMN_NAME, saveDate(entity.getDate()));
longid= db.insertOrThrow(TABLE_NAME, null, values);

3) Use this utility method to load date,

publicstaticDateloadDate(Cursor cursor, int index) {
    if (cursor.isNull(index)) {
        returnnull;
    }
    returnnewDate(cursor.getLong(index));
}

like,

entity.setDate(loadDate(cursor, INDEX));

4) You can also order the data by date using simple ORDER clause,

publicstaticfinalStringQUERY="SELECT table._id, table.dateCol FROM table ORDER BY table.dateCol DESC";

//...Cursorcursor= rawQuery(QUERY, null);
    cursor.moveToFirst();

    while (!cursor.isAfterLast()) {
        // Process results
    }