sqlite3怎么筛选一个月之前的,SQLite数据库-选择两个日期之间的数据？

I want to select my data by date - from a date until another date, so I have this query,

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09' AND '2014-10-10'

But this query only return the data in '2014-10-09', excluding the data in '2014-10-10', unless I change the query to this below,

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09' AND '2014-10-11'

This is not an ideal solution. How can I select the data including the data in '2014-10-10'?

NOTE:

I think my problem is different from other duplicate questions becos,

my date type is TEXT

I need to select the date's data without its time.

it is a sqlite database...

My data sample...

sid nid timestamp date

1 20748 5 1412881193 2014-10-09 14:59:53

2 20749 5 1412881300 2014-10-09 15:01:40

3 20750 5 1412881360 2014-10-09 15:02:40

解决方案

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: 0

mysql> 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';