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.
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.
If field created_at is TIMESTAMP:
COUNT(DISTINCT FROM_UNIXTIME(created_at, '%Y-%m-%d')) as days
or if field is DATETIME:
COUNT(DISTINCT DATE(created_at)) as days
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments