I am trying to only grab records that fall in a certain date range. The problem is that the timestamp and the date are stored as a string in the same cell. I want to only grab rows with a date that falls betweed 2013-05-01 and 2013-05-03.
我试图只抓取属于某个日期范围的记录。问题是时间戳和日期作为字符串存储在同一个单元格中。我想只抓住日期在2013-05-01和2013-05-03之间的行。
date (stored as string)
日期（存储为字符串）
2013-05-01T23:19:44
2013-05-02T23:19:40
2013-05-06T23:19:46
2013-05-06T23:15:17
mysql
MySQL的
SELECT * FROM table WHERE date BETWEEN 2013-05-01 AND 2013-05-03
4 个解决方案
#1
10
Try
尝试
SELECT *
FROM table1
WHERE STR_TO_DATE(`date`,'%Y-%m-%d') BETWEEN '2013-05-01' AND '2013-05-03'
SQLFiddle
As @FreshPrinceOfSO absolutely correctly noted no index will be used in that case
由于@FreshPrinceOfSO绝对正确地指出在这种情况下不会使用索引
#2
1
SELECT * FROM table1 WHERE STR_TO_DATE(SUBSTRING(`date`,1,10),'%d-%m-%Y') BETWEEN '2013-05-01' AND '2013-05-03'
#3
0
The string is almost valid syntax for a datetime. Thus, an alternative, if perhaps slower method is to replace the 'T' with a space and then cast it to a datetime.
该字符串几乎是日期时间的有效语法。因此，另一种方法，如果可能更慢的方法是用空格替换'T'然后将其转换为日期时间。
SELECT * FROM table1 WHERE
CAST(REPLACE(`date`, 'T', ' ') AS DATETIME)
BETWEEN '2013-05-01' AND '2013-05-03';
#4
0
SELECT * FROM table WHERE date('yourdate') BETWEEN date('2013-05-01') AND date('2013-05-03')
SELECT * FROM table WHERE date（'yourdate'）BETWEEN date（'2013-05-01'）AND date（'2013-05-03'）
#1
10
Try
尝试
SELECT *
FROM table1
WHERE STR_TO_DATE(`date`,'%Y-%m-%d') BETWEEN '2013-05-01' AND '2013-05-03'
SQLFiddle
As @FreshPrinceOfSO absolutely correctly noted no index will be used in that case
由于@FreshPrinceOfSO绝对正确地指出在这种情况下不会使用索引
#2
1
SELECT * FROM table1 WHERE STR_TO_DATE(SUBSTRING(`date`,1,10),'%d-%m-%Y') BETWEEN '2013-05-01' AND '2013-05-03'
#3
0
The string is almost valid syntax for a datetime. Thus, an alternative, if perhaps slower method is to replace the 'T' with a space and then cast it to a datetime.
该字符串几乎是日期时间的有效语法。因此，另一种方法，如果可能更慢的方法是用空格替换'T'然后将其转换为日期时间。
SELECT * FROM table1 WHERE
CAST(REPLACE(`date`, 'T', ' ') AS DATETIME)
BETWEEN '2013-05-01' AND '2013-05-03';
#4
0
SELECT * FROM table WHERE date('yourdate') BETWEEN date('2013-05-01') AND date('2013-05-03')
SELECT * FROM table WHERE date（'yourdate'）BETWEEN date（'2013-05-01'）AND date（'2013-05-03'）