How to get each day's information using group by keyword in mysql

I'm new in mysql and I'm currently having an issue with a query. I need to get an average duration for each activity each day within a week. The date format is like:开发者_如何转开发 '2000-01-01 01:01:01', but I want to get rid of the 01:01:01 thing and only care about the date. How do I do that?

The table is something like this:

record_id   int(10)      NOT NULL,
activity_id varchar(100) NOT NULL,
start_time  datetime     NOT NUll,
end_time    datetime     NOT NULL,
duration    int(10)      NOT NULL;

Thanks.

---

You could do something like the following

select activity_id, dayofweek(datetime) as day, avg(duration) as average
from table_name  where datetime between start_date and end_date
group by activity_id,dayofweek(datetime)

---

If I'm understanding, you want to group by the different activity times and see the average days between the start and end of each activity. This should do it for you.

SELECT activity_id, avg(DATEDIFF(end_time, start_time)) AS Average 
FROM tablename 
GROUP BY activity_id, DAYOFWEEK(start_time)

Edit: Misunderstood, you want it broken down by the day as well, so this should pull each group, broken down by the day of the week that the start_time falls on, and then the average days between start_time and end_time.

---

You can use date_format(end_time, '%Y%m%d') to convert it to a sortable value by day. Put that in the group by expression, and that should do what you want.