sqlite date compare: Any idea why the following sqlite select with time > date('now') doesn't return the collect row?

tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db "select * from  users where timeCreate > date('now')"
1|test0开发者_运维知识库@test.com|testuser|test_pass|0|2011-07-01 05:49:19.365472|2011-07-01 05:49:19.395194
2|test1@test.com|testuser1|test_pass_new|10|2011-07-01 05:49:19.376098|2011-07-01 05:49:19.394416
3|test_1@test.com|testuser_1|test_pass_1|2|2011-07-01 05:49:19.407550|2011-07-01 05:49:19.407550


tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db "SELECT datetime('now'); "                      2011-07-01 06:22:44

tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db "select * from  users where timeCreate < date('now')"

tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db "select * from  users where timeCreate < date('now', '-5 minutes')"

tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db ".dump"
BEGIN TRANSACTION;
CREATE TABLE users
                (user_id    INTEGER PRIMARY    KEY,
                 email      text,
                 userName   text,
                 password   text,
                 usertype   int,
                 timeCreate DATE,
                 lastLogin  DATE);
INSERT INTO "users" VALUES(1,'test0@test.com','testuser','test_pass',0,'2011-07-01 05:49:19.365472','2011-07-01 05:49:19.395194');
INSERT INTO "users" VALUES(2,'test1@test.com','testuser1','test_pass_new',10,'2011-07-01 05:49:19.376098','2011-07-01 05:49:19.394416');
....

Just try this query, it still doesn't work:

tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db "select datetime(timeCreate) from  users "
2011-07-01 05:49:19
2011-07-01 05:49:19
tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db "select * from  users where datetime(timeCreate) < date('now', '-5 minutes')"
tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db "select * from  users where datetime(timeCreate) > date('now', '-5 minutes')"
1|test0@test.com|testuser|test_pass|0|2011-07-01 05:49:19.365472|2011-07-01 05:49:19.395194
2|test1@test.com|testuser1|test_pass_new|10|2011-07-01 05:49:19.376098|2011-07-01 05:49:19.394416
tlee@tlee-vm:~/work/hl$ sqlite3 sdb/testUsersSDB.db "SELECT datetime('now', '-5 minutes'); "
2011-07-01 08:24:06

I expect the "<" query to return the row and not the ">".

You are comparing only the date with a datetime as the date function returns a date not a date with time:

sqlite> select date('now', '-5 minutes');
2011-07-07

So just use the datetime function instead:

sqlite> select datetime('now', '-5 minutes');
2011-07-07 17:23:29