仅从 MySQL 中的日期时间 (YYYY-MM-DD HH:MM:SS) 中选择不同的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14844186/
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
Selecting a distinct date by day only from datetime (YYYY-MM-DD HH:MM:SS) in MySQL
提问by eveo
Executing this command brings me the following (all the dates from all columns, so it essentially did the same thing as SELECT date without distinct):
执行这个命令给我带来了以下内容（所有列的所有日期，所以它本质上与 SELECT date 没有区别）做了同样的事情：
SELECT DISTINCT date FROM daily ORDER BY date DESC
2013-02-12 16:40:52
2013-02-06 11:48:49
2013-02-06 11:36:41
2013-02-06 11:35:59
2013-02-04 19:38:12
2013-02-04 18:12:30
2013-02-04 09:58:41
2013-02-04 09:43:01
2013-02-04 09:35:51
2013-02-04 09:30:22
2013-02-04 09:24:57
2013-02-04 09:21:09
2013-02-04 08:50:13
What I need:
我需要的：
2013-02-12
2013-02-06
2013-02-04
Is there any way to alter my date table and convert it to YYYY-MM-DD instead?
If not, is there a way to select distinct dates based only on the day?
有什么办法可以更改我的日期表并将其转换为 YYYY-MM-DD 吗？
如果没有，有没有办法仅根据当天选择不同的日期？
回答by Michael Durrant
mysql> select DATE_FORMAT(Current_Timestamp, '%c %d %Y') from dual;
+--------------------------------------------+
| DATE_FORMAT(Current_Timestamp, '%c %d %Y') |
+--------------------------------------------+
| 2 12 2013 |
+--------------------------------------------+
1 row in set (0.01 sec)
mysql>
of course you would be using your 'daily' table.
当然，您将使用“每日”表。
mysql> select DATE_FORMAT(Date, '%c %d %Y') from daily;
or maybe you want
或者你可能想要
mysql> select * from daily group by DATE_FORMAT(Date, '%c %d %Y');
回答by Vincent
So I have just added a small part to sgeddes answer which was close to working for me but I had to as the : AS date_change so I can list the values.
所以我刚刚在 sgeddes 答案中添加了一小部分，这对我来说很接近，但我不得不作为：AS date_change 以便我可以列出这些值。
$sql = "SELECT DISTINCT DATE_FORMAT(yourdate, '%Y-%m-%d') as date_change FROM
daily ORDER BY (yourdate) DESC LIMIT 10";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$test = $row['date_change'];
echo $test.'<br>';
}
} else {
echo "0 results";
}
回答by Nilesh Nikumbh
Try this one:
试试这个：
SELECT DISTINCT(CONVERT(VARCHAR, CONVERT(DATETIME,[DATE]),23)) AS DT
FROM DAILY
ORDER BY DT ASC
回答by PodTech.io
I have found on large data sets > N millions, grouping by DATE_FORMAT can be 10-20% slower than using;
我发现在大于 N 百万的大型数据集上，按 DATE_FORMAT 分组可能比使用慢 10-20%；
GROUP BY someid,year(date),month(date),day(date)
回答by FC3D
In Access DB
在访问数据库中
SELECT DISTINCT Format(date, 'Short Date')
FROM daily
ORDER BY Format(date, 'Short Date') DESC;
回答by sgeddes
For MySQL, you can use DATE(date)to return just the date. If you want to format it, use DATE_FORMAT:
对于 MySQL，您可以使用DATE(date)仅返回日期。如果要格式化，请使用DATE_FORMAT：
SELECT DISTINCT DATE_FORMAT(yourdate, '%Y-%m-%d')
FROM daily ORDER BY DATE(yourdate) DESC
You cannot change the way the database stores these values.
您无法更改数据库存储这些值的方式。
And here is the fiddle: http://sqlfiddle.com/#!2/f50527/6
