Counting individual days with DateTime in MySQL

Question

I have a DB table that registers a query using an IP column and a DateTime stamp. I am trying to extract this data so that I count the number of days when a specific IP address made requests. I am using the Laravel query constructor.

So far this is what I have:

$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 contains hours, minutes and seconds. Thus, the calculation of "days" will be approximately the same as the number of complete requests. I only want to count the number of active days.

Answer 1

If the field created_atis TIMESTAMP:

COUNT(DISTINCT FROM_UNIXTIME(created_at, '%Y-%m-%d')) as days

or if the DATETIME field:

COUNT(DISTINCT DATE(created_at)) as days

Answer 2

, DATE_FORMAT, , . : (-- : : ) MySQL