CODEWITHSUNDEEP
sqlite
tony-p-lee
tony-p-lee

Reputation: 807

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|[email protected]|testuser|test_pass|0|2011-07-01 05:49:19.365472|2011-07-01 05:49:19.395194
2|[email protected]|testuser1|test_pass_new|10|2011-07-01 05:49:19.376098|2011-07-01 05:49:19.394416
3|[email protected]|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,'[email protected]','testuser','test_pass',0,'2011-07-01 05:49:19.365472','2011-07-01 05:49:19.395194');
INSERT INTO "users" VALUES(2,'[email protected]','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|[email protected]|testuser|test_pass|0|2011-07-01 05:49:19.365472|2011-07-01 05:49:19.395194
2|[email protected]|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 ">".

Upvotes: 0

Views: 1469

Answers (1)

user610650
user610650

Reputation:

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

Upvotes: 2

Related Questions