Search code examples
androidsqlsqliteandroid-sqliteunix-timestamp

sqlite - get all entries from today

I have an android app with sqlite database which has a column holding the entry creation date in unix-time format (DateTime().getMillis() / 1000).

What I want is very simple, to get all the entries from today.

Just to be clear, I mean all entries bigger than at 00:00:00 of today (just after midnight of yesterday).

I guess my timezone should also go in the query somewhere, because unix-time is in UTC.

Solution

  • The current Unix timestamp can be computed with:

    strftime('%s', 'now')

    To go to the start of the day, add a modifier:

    strftime('%s', 'now', 'start of day')

    However, this uses 00:00:00 in the UTC timezone, so you have to convert the timestamp to the local time zone, go to that start of the date, and convert it back:

    strftime('%s', 'now', 'localtime', 'start of day', 'utc')

    And strftime returns a string, so you should convert its result into a number:

    ... WHERE EntryCreationDate >= 0 + strftime(...)