SQLite数据库-选择两个日期之间的数据? [英] SQLite database - select the data between two dates?
问题描述
我想按日期选择数据-从一个日期到另一个日期,所以我有这个查询,
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'
但是此查询仅返回"2014-10-09"中的数据,不包括"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'
这不是理想的解决方案.如何选择包括"2014-10-10"中的数据的数据?
This is not an ideal solution. How can I select the data including the data in '2014-10-10'?
注意:
我认为我的问题不同于其他重复问题becos,
I think my problem is different from other duplicate questions becos,
- 我的日期类型为TEXT
- 我需要选择日期数据而不需要时间.
- 这是一个sqlite数据库...
我的数据样本...
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
推荐答案
您也可以不使用between
.
select * from mytable where `date` >= '2014-10-09' and `date` <= '2014-10-10'
示例:
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';
问题再次更新,其他答案
U.您有时间戳记字段吗?在这种情况下:
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'
最终我们到达了sqlite
select date(datetime(timestamp, 'unixepoch'))
from mytable
where date(datetime(timestamp, 'unixepoch'))
between '2014-10-09' and '2014-10-10';
这篇关于SQLite数据库-选择两个日期之间的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!