CODEWITHSUNDEEP
mysqlsql
Rich Bradshaw
Rich Bradshaw

Reputation: 72985

Count rows matching different intervals of a unix time stamp (MYSQL)

I have a MYSQL table recording hits to a website it is of the form (and this can change if required, I'm in the 'is this feasible' stage of the project):

+----+-----------+---------+----------+------------+
| id | useragent |   url   | loadTime |  unixtime  |
+----+-----------+---------+----------+------------+
|  1 | Chrome    | /       |      300 | 1334354015 |
|  2 | Chrome    | /       |      330 | 1334354020 |
|  3 | Opera     | /about/ |      310 | 1334354100 |
+----+-----------+---------+----------+------------+

etc.

What I'd like to do is to count hits for each day, as well as averaging the loadTime per day and other things I'll do later.

To count the hits, the only way I know how to do it is to use a serverside language to SELECT *, then loop through, counting the number of rows with the unix time between the start and end of the day. What's the proper SQL way to do this, or should I stick to select *, loop?

Upvotes: 0

Views: 373

Answers (1)

xarion
xarion

Reputation: 470

assuming that your table name is visits. 

select date(FROM_UNIXTIME(unixtime)) as visitDate, count(v.id) from visits v group by visitDate;

should work just fine .. if you want to filter the results by page url;

select date(FROM_UNIXTIME(unixtime)) as visitDate, url, count(v.id) from visits v group by visitDate, url;

and if you want to see the visits of today;

select date(FROM_UNIXTIME(unixtime)) as visitDate, count(v.id) from visits v where date(now()) = date(FROM_UNIXTIME(unixtime)) group by visitDate;

I havent tested any of these, might require a little bit modification.

Upvotes: 2

Related Questions