MySQL 从表中的日期时间列中选择不同的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5335735/
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 distinct dates from datetime column in a table
提问by rvk
I have a table
我有一张桌子
id | message | date_posted
1 | testing | 2011-03-08 03:15:13
2 | testing | 2011-03-06 03:15:13
3 | testing | 2011-03-08 03:15:13
And need a query where I return a list of distinct dates in desc order. I tried to formulate a query like so
并且需要一个查询，在那里我按降序返回不同日期的列表。我试图制定一个这样的查询
SELECT DISTINCT CAST(`date_posted` AS DATE) AS dateonly FROM table
This gives me dates but they're not distinct and have duplicates.
这给了我日期，但它们并不明显并且有重复。
Any ideas? (Using php/mysql)
有任何想法吗？(使用php/mysql)
MAJOR EDIT:
主要编辑：
Forgot an important piece of information. I'm trying to get unique dates based on month and year.
忘记了一条重要信息。我正在尝试根据月份和年份获取唯一日期。
2011-03-08 03:15:13
2011-03-06 03:15:13
2011-03-02 03:15:13
2011-03-01 03:15:13
2011-02-01 03:15:13
So running the query would only return [2011-03-dd,2011-02-01] (dd being any day)
所以运行查询只会返回 [2011-03-dd,2011-02-01] （dd 是任何一天）
Apologize for not stating this.
为没有说明这一点而道歉。
回答by Hassan
Your query will return unique DATES, as you've specified. If you want to get just unique MONTHS/YEARS, you should either modify you dates in a way, that each date becomes the first day of the month, or you should just go with some string representation like 'YYYY-MM'. Sorry, I can't write you an exact code how to do this, as I do not develop on mysql, but I think this should get you somewhere :)
您的查询将返回您指定的唯一日期。如果您只想获得唯一的 MONTHS/YEARS，您应该以某种方式修改您的日期，使每个日期成为该月的第一天，或者您应该使用一些字符串表示，例如“YYYY-MM”。抱歉，我无法为您编写如何执行此操作的确切代码，因为我不在 mysql 上开发，但我认为这应该可以帮助您：)
SELECT DISTINCT YEAR(date_posted), MONTH(date_posted) FROM table
回答by Pentium10
SELECT DATE(`date_posted`) AS dateonly
FROM table
GROUP BY DATE(`date_posted`)
回答by RichardTheKiwi
Working example
工作示例
create table dts (dt datetime);
insert dts select '2011-03-08 03:15:13';
insert dts select '2011-03-07 03:15:13';
insert dts select '2011-03-09 03:15:13';
insert dts select '2011-03-08 03:15:13';
select distinct cast(dt as date) from dts;
Output
输出
"cast(dt as date)"
"2011-03-08"
"2011-03-07"
"2011-03-09"
Query for distinct Year-Month
查询不同的年月
select date(date_format(dt, '%Y-%m-1')) as dt
from dts
group by dt;
Output (the result is a date column, with the day set to '1')
输出（结果是日期列，日期设置为“1”）
2011-03-01
回答by sparsh turkane
SELECT DISTINCT date(date_format(COLUMN_NAME, '%Y-%m-%d')) as uniquedates from TABLE_NAME
