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
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.
just a quick guess of what you are asking for:
select date(timestamp),count(*) from logs group by date(FROM_UNIXTIME(timestamp))
example output from a database I tested the query on:
+---------------+----------+
| date(timestamp) | count(*) |
+---------------+----------+
| 2013-12-13 | 4978 |
| 2013-12-14 | 5016 |
| 2013-12-15 | 4999 |
| 2013-12-16 | 5002 |
| 2013-12-17 | 4995 |
| 2013-12-18 | 5000 |
| 2013-12-19 | 5001 |
| 2013-12-20 | 5001 |
| 2013-12-21 | 5001 |
| 2013-12-22 | 4713 |
| 2013-12-30 | 4892 |
| 2013-12-31 | 5064 |
| 2014-01-01 | 5024 |
| 2014-01-02 | 5001 |
| 2014-01-03 | 5002 |
| 2014-01-04 | 5001 |
| 2014-01-05 | 4970 |
| 2014-01-06 | 4989 |
| 2014-01-07 | 4995 |
| 2014-01-08 | 5004 |
| 2014-01-09 | 2531 |
To get the busiest day use DAYOFWEEK
SELECT DAYOFWEEK(timestamp),COUNT(*) FROM logs GROUP BY DAYOFWEEK(timestamp)