CODEWITHSUNDEEP
sqlitedateselecttime
xralf
xralf

Reputation: 3642

Date function in SQLite does not select rows that are in a database

I have a column c in table tbl which contains the following values.

Tue, 15 Nov 2011 14:21:32 GMT
Tue, 15 Nov 2011 12:59:21 GMT
Tue, 15 Nov 2011 12:15:48 GMT
Tue, 15 Nov 2011 12:10:33 GMT
Tue, 15 Nov 2011 11:12:09 GMT
Tue, 15 Nov 2011 11:09:14 GMT
Tue, 15 Nov 2011 10:38:01 GMT
Tue, 15 Nov 2011 10:36:33 GMT
Tue, 15 Nov 2011 10:09:21 GMT
Tue, 15 Nov 2011 09:23:01 GMT
Tue, 15 Nov 2011 08:24:39 GMT
Mon, 14 Nov 2011 22:44:53 GMT
Mon, 14 Nov 2011 21:03:28 GMT
Mon, 14 Nov 2011 20:12:11 GMT
Mon, 14 Nov 2011 19:08:34 GMT
Mon, 14 Nov 2011 18:40:05 GMT
Mon, 14 Nov 2011 18:07:00 GMT
Mon, 14 Nov 2011 18:04:00 GMT
Mon, 14 Nov 2011 17:37:45 GMT

These values were taken from command:

select c from tbl;

When I run:

select count(*) from tbl where c < date('2011-11-18');

the result is 0

What is wrong here?

thank you

Upvotes: 1

Views: 529

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 50970

What you have stored in your database are regular strings, not SQLite datetime strings.

For the contents to TEXT column to be recognized as a datetime it must be stored in the format YYYY-MM-DD HH:MM:SS.SSS.

To get the results you want out of the data you have, you will have to use string comparisons, and doing the < comparison that you want will be tricky (you'll need to examine substrings and look for year < 2011 OR month IN (JAN,FEB,MAR. . .) OR (month = 'NOV' AND day < 18).

Better to:

  1. Find where the data is inserted and change it to insert a "proper" SQLite date and

  2. Run a one-time routine over the existing data to reformat it.

Upvotes: 1

Serhii Mamontov
Serhii Mamontov

Reputation: 4932

First of all, you have to do some manipulation on date string, before adding it into the database. Basically it should contain only: year, month, day, hour, minutes, seconds, millisecond (optional). All data is in form of number, no strings should be inside.

I've created small table: 

> sqlite3 ':memory:'
sqlite> CREATE TABLE tbl (c text);
sqlite> INSERT INTO tbl (c) VALUES ('2011-11-15 14:21:32');
sqlite> INSERT INTO tbl (c) VALUES ('2011-11-15 12:59:21');
sqlite> INSERT INTO tbl (c) VALUES ('2011-11-15 12:15:48');
sqlite> INSERT INTO tbl (c) VALUES ('2011-11-15 12:10:33');
sqlite> INSERT INTO tbl (c) VALUES ('2011-11-15 11:12:09');
sqlite> INSERT INTO tbl (c) VALUES ('2011-12-15 11:12:09');

sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S', c) FROM tbl WHERE strftime('%Y-%m-%d %H:%M:%S', c) < strftime('%Y-%m-%d', '2011-12-15');

Gives me nice result: 

2011-11-15 14:21:32
2011-11-15 12:59:21
2011-11-15 12:15:48
2011-11-15 12:10:33
2011-11-15 11:12:09

Upvotes: 2

Related Questions