Search code examples
sqliteunix-timestamp

How to Query for Recent Rows in SQLITE3

I'm using SQLite3 and trying to query for recent rows. So I'm having SQLite3 insert a unix timestamp into each row with strftime('%s','now'). My Table looks like this:

CREATE TABLE test(id INTEGER PRIMARY KEY, time);

INSERT INTO test (time) VALUES (strftime('%s','now')); --Repeated


SELECT * FROM test;
1|1516816522
2|1516816634
3|1516816646 --etc lots of rows

Now I want to query for only recent entries, for example, I'm trying to get all rows with a time within the last hour. I'm trying the following SQL query:

SELECT * FROM test WHERE time > strftime('%s','now')-60*60;

However, that always returns all rows regardless of the value in the time column. I really don't know what's going on.

Also, if I put WHERE time > strftime('%s','now') it'll return nothing (which is expected) but if I put WHERE time > strftime('%s','now')-1 then it'll return everything. I don't know why.

Here's one more example:

sqlite> SELECT , strftime('%s','now')-1 AS window FROM test WHERE time > window;
1|1516816522|1516817482
2|1516816634|1516817482
3|1516816646|1516817482

It seems that SQLite3 thinks the values in the middle column are greater than the values in the right column!?

This isn't at all what I expect. Can someone please tell me what's going on? Thanks!

Solution

  • The purpose of strftime() is to format values, so it returns a string.

    When you try to do computations with its return value, the database must convert it into a number. And numbers and strings cannot be compared directly with each other.

    You must ensure that both values in a comparison have the same data type. The best way to do this is to store numbers in the table:

    INSERT INTO test (time)
VALUES (CAST(strftime('%s','now') AS MAKE_THIS_A_NUMBER_PLEASE));

    (Or just declare the column type as something with numeric affinity.)