Counting distinct days from DateTime in MySQL

Question

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.

Answer 1

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

Answer 2

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