Search code examples
sqliteandroid-sqlite

Why does SQLite consider larger number < smaller number to be true?

Querying the SQLite database of my Android app, I'm running into a curious problem. I've got a query that checks one epoch time (stored in the database in an integer field) against the current epoch time. To simplify debugging, I wrote a little test query to see how SQLite handles my epoch integer versus the strftime function. Here's my query:

select 1615759200 < strftime('%s', 'now');

The result is:

1

For reference: the value 1615759200 is the date March 14, 2021. The current date at time of writing is April 28, 2020 which is roughly 1588033692 in epoch time. For obvious reasons, I'm expecting the above query to result 0: false, as the date in 2021 is NOT smaller than the date in 2020. And yet, it returns 1: true! It's infuriating! Can anyone tell me what I'm missing here?

Solution

  • The data type that is returned by strftime() is text and you must cast it to an integer so that the comparison will work the way that you expect:

    select 1615759200 < cast(strftime('%s', 'now') as int)

    or:

    select 1615759200 < strftime('%s', 'now') + 0

    You can find more about data types and affinities of SQLite: Datatypes In SQLite Version 3