laravel 从 MySQL 中的 DateTime 计算不同的天数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19090129/
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
Counting distinct days from DateTime in MySQL
提问by Stromgren
I have DB table that logs request with an IP column and a DateTime stamp. I'm trying to fetch this data in a way that makes me count the number of days a certain IP has made requests. I'm using Laravel's query builder.
我有一个数据库表，它用一个 IP 列和一个日期时间戳记录请求。我试图以一种让我计算某个 IP 发出请求的天数的方式获取这些数据。我正在使用 Laravel 的查询构建器。
So far, this is what i've got:
到目前为止，这就是我所拥有的：
$data = DB::table('requests')
->groupBy('ip')
->select('ip',
DB::raw('COUNT(DISTINCT created_at) as days'),
DB::raw('COUNT(*) as requests'))
->orderBy('days', 'desc')
->take(50)
->get();
My problem is that the timestamp also holds hours, minutes and seconds. So the "days" count will be about the same as the number of total requests. I want to only count the number of days active.
我的问题是时间戳也包含小时、分钟和秒。所以“天”数将与总请求数大致相同。我只想计算活动天数。
回答by Glavi?
If field created_atis TIMESTAMP:
如果字段created_at是TIMESTAMP：
COUNT(DISTINCT FROM_UNIXTIME(created_at, '%Y-%m-%d')) as days
or if field is DATETIME:
或者如果字段是DATETIME：
COUNT(DISTINCT DATE(created_at)) as days
回答by joakaune
You can probably use DATE_FORMAT to do what you want. Take a look at this question: Selecting a distinct date by day only from datetime (YYYY-MM-DD HH:MM:SS) in MySQL
您可能可以使用 DATE_FORMAT 来做您想做的事。看看这个问题： Selecting a distinct date by day only from datetime (YYYY-MM-DD HH:MM:SS) in MySQL
