Search code examples
javaandroidsqlitejulian-date

How do I find out how many days have passed since the date saved in the SQLite table?

I'm writing my first Android app. I save the data to my SQLite database like this:

    ContentValues values = new ContentValues();
    values.put("mydate", System.currentTimeMillis());
    mydatabase.update("mytable", values, "id = ?", new String[]{String.valueOf(w.Id())});

Then I am to get how many days have passed from that day. I tried this:

SELECT ((julianday('now') - 2440587.5)*86400000 - mydate)/1000 * 60 * 60 * 24 AS days FROM mytable

And get some absurd numbers. It's because of absence of julianday() function in old Java versions I cannot get this value by simple

SELECT julianday('now') - mydate AS days

And how can I get this in old Java?

Solution

  • I found it! I am to save in my database every date as 'YYYY-MM-DD'.Then simply:

    SELECT (strftime('%s', 'now') - strftime('%s', mydate)) / 86400.0 from mytable