SQLite database - select the data between two dates?
IF date is a timestamp, you'll need to do like:
SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09 00:00:00' AND '2014-10-10 23:59:59'
Or you can do, I believe:
SELECT * FROM mytalbe WHERE DATE(date) BETWEEN '2014-10-09' AND '2014-10-10'
Or, since it is a text field:
SELECT * FROM mytalbe WHERE DATE_FORMAT(date,'%Y-%m-%d') BETWEEN '2014-10-09' AND '2014-10-10'
You could also just not use between
.
select * from mytable where `date` >= '2014-10-09' and `date` <= '2014-10-10'
Example:
mysql> create table dd (id integer primary key auto_increment, date text);Query OK, 0 rows affected (0.11 sec)mysql> insert into dd(date) values ('2014-10-08'), ('2014-10-09'), ('2014-10-10'), ('2014-10-11');Query OK, 4 rows affected (0.05 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from dd where date >= "2014-10-09" and date <= "2014-10-10";+----+------------+| id | date |+----+------------+| 2 | 2014-10-09 || 3 | 2014-10-10 |+----+------------+2 rows in set (0.01 sec)
Since it includes time, and you dont want the time. this:
select substring(date, 1, 10) from dd where substring(date, 1, 10) between '2014-10-09' and '2014-10-10';
question updated again, additional answer
Ugh. you have timestamp fields? in that case this:
select date(from_unixtime(timestamp)) from mytabel where date(from_unixtime(timestamp)) between '2014-10-09' and '2014-10-10'
finally we have arrived at sqlite
select date(datetime(timestamp, 'unixepoch')) from mytable where date(datetime(timestamp, 'unixepoch')) between '2014-10-09' and '2014-10-10';