SQL SQLite 数据库 - 选择两个日期之间的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29971762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite database - select the data between two dates?
提问by laukok
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,
但是这个查询只返回'2014-10-09'中的数据，不包括'2014-10-10'中的数据，除非我把查询改成下面这个，
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'?
这不是一个理想的解决方案。如何选择包含“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...
- 我的日期类型是 TEXT
- 我需要选择没有时间的日期数据。
- 它是一个sqlite数据库...
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
采纳答案by pala_
You could also just not use between.
你也可以不使用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
终于我们到了sqlite
select date(datetime(timestamp, 'unixepoch'))
from mytable
where date(datetime(timestamp, 'unixepoch'))
between '2014-10-09' and '2014-10-10';
回答by dgig
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'
回答by Toni A.
same problem solved, thanks. my code :
同样的问题解决了，谢谢。我的代码：
var FechaInicio = dtpFechaInicial.Value.ToString("yyyy-MM-dd");
var FechaFinal = dtpFechaFinal.Value.ToString("yyyy-MM-dd");
string SQLcmd = $"SELECT * FROM sorteos WHERE DATE(fecha) BETWEEN ('{FechaInicio}') AND ('{FechaFinal}')";
