SQLite 数据库 - 选择两个日期之间的数据?

2021-12-19 00:00:00 sql select 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'

但是这个查询只返回'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'?

注意:

我认为我的问题与其他重复问题不同，

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

  1. 我的日期类型是文本
  2. 我需要选择不含时间的日期数据.
  3. 它是一个 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';

问题再次更新，补充答案

呃.你有时间戳字段吗?在这种情况下:

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

相关文章