使用PHP解决最忙碌的一天

Question

I have a table with a timestamp in unixtimestamp for everytime someone logs into the system. I would like to work out the busiest day.

Im a little lost as to where to start, i believe its that you need to work out a count for each day stored into the database (or some kind of string between start and end of the logs?)

Database Structure for logs

Example :

user  | action         | timestamp
admin | User Logged In | 1395257767

User being "Username"

Action being "User Logged In"

Timestamp being Unixtimestamp given at the time they login to the system.

Any help would be a great start

SQLFiddle - http://sqlfiddle.com/#!2/66d4293

Answer 1

Try something like thi.

SELECT COUNT(*) c, DATE(FROM_UNIXTIME( timestamp) ) day FROM log 
GROUP BY  day ORDER BY c DESC LIMIT 1;

DATE(FROM_UNIXTIME( timestamp )) converts to a datetime and then a date.

By the way, you might consider storing your dates as datetime or timestamp structures. Storing as unix timestamps and then having to convert to useable dates is less than ideal.

http://sqlfiddle.com/#!2/a53d1/2