Page 1 of 1

Trending over time with MySQL (and maybe a bit of PHP)

Posted: Thu Jul 02, 2009 8:37 pm
by Griven
Hey all,

In order to gain usage statistics on our corporate intranet, I've created a "visits" table that is updated whenever someone views a page on our intranet. Its columns are the following:

visit_token - An MD5 hash value that is unique to the current session. It is carried across all page views until the user closes the browser and the server dumps the session.
visit_ip - the ip address of the remote host
visit_user - the domain\username of the visiting user
visit_page - the page that the user is visiting
visit_time - the timestamp of the page view

My goal is to figure out the average number of users that are using the intranet at any given point in the day by taking a time sampling of ten minutes or so. I'd like to get a count of the unique token values for a span of ten minutes.

This is easy to do with a simple "between" query. However, I'd like to do it for ten minute intervals throughout the entire day, so that I can make a decent graph with it.

Is there a way to do this with raw SQL, or will I need to do some PHP coding?

Or perhaps someone here has a more inspring idea of how to go about this?

Any help on this matter would be much appreciated.

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 1:12 am
by VladSun
[ If visit_time is DATETIME convert it to UNIXTIME ]

Perform a resampling to 10 minutes and GROUP BY this value.

EDIT: <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> removed :)

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 1:56 am
by Griven
Thanks for the reply, VladSun. I have a couple of questions regarding your response.

1. What is the purpose of converting the DATETIME column to UNIXTIME?

2. How do I do that resampling? e.g. How do I get a result set that contains a count of the unique visit tokens for every ten minute interval within a period of time (24 hours, for example)? I've got a query that will give me this for only one ten minute interval, but not all of them throughout the specified time period.

Code: Select all

SELECT DISTINCT(COUNT(visit_token)), visit_time FROM visits WHERE visit_time BETWEEN '2009-06-26 00:00:00' AND  '2009-06-26 00:10:00'
Obviously, this isn't very flexible, and may be very far off from what I need.

I had an idea of creating a loop that would do this over and over and put the resulting rows into another table, but didn't get a chance to flesh that out before I left work for the weekend. Do you think that might do the trick?

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 1:58 am
by Griven
Thanks for the reply, VladSun. I have a couple of questions regarding your response.

1. What is the purpose of converting the DATETIME column to UNIXTIME?

2. How do I do that resampling? e.g. How do I get a result set that contains a count of the unique visit tokens for every ten minute interval within a period of time (24 hours, for example)? I've got a query that will give me this for only one ten minute interval, but not all of them throughout the specified time period.

Code: Select all

SELECT DISTINCT(COUNT(visit_token)), visit_time FROM visits WHERE visit_time BETWEEN '2009-06-26 00:00:00' AND  '2009-06-26 00:10:00'
Obviously, this isn't very flexible, and may be very far off from what I need.

I had an idea of creating a loop that would do this over and over and put the resulting rows into another table, but didn't get a chance to flesh that out before I left work for the weekend. Do you think that might do the trick?

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 2:16 am
by VladSun
Try
[sql]SELECT UNIX_TIMESTAMP(visit_time) FROM visits[/sql]
and see what you get.

Then think how to transform these series values into a a series of values having 10 minutes accuracy.
It's like a simple math problem - having these values

56, 59, 64, 74, 93 ... etc.

to transform them to :

50, 50, 60, 70, 90 ... etc.

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 3:50 am
by Griven
Thanks for the prompt followup!

Based on your suggestion, this is what I came up with:

Code: Select all

SELECT COUNT(DISTINCT(visit_token)),
FROM_UNIXTIME(CONCAT(SUBSTRING(UNIX_TIMESTAMP(visit_time),1,7),'000'),'%Y-%m-%d %k:%i:%s') AS time_stamp
FROM visits
WHERE visit_time BETWEEN '2009-06-26 00:00:00' AND '2009-06-27 00:00:00'
GROUP BY time_stamp
ORDER BY visit_time ASC
It gives me pretty much what I want, except that I can't get exact ten minute intervals. This particular query gives me 16 minutes and 40 second intervals. In any case, the data points make a nice graph to give to the boss (look, pretty pictures!).

Do you have any suggestions on how to improve this query? I'm sure my time conversion to and from unix time (for readability) up there is pretty hairy.

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 4:28 am
by onion2k
[sql]SELECT COUNT(`visit_token`) AS hits, FLOOR(((HOUR(`visit_time`)*60)+MINUTE(`visit_time`))/10) AS t FROM `acu_log` WHERE `visit_time` BETWEEN '2009-06-26 00:00:00' AND '2009-06-27 00:00:00'GROUP BY t ORDER BY `visit_time` ASC[/sql]
I think that should work. It'll break the groups down into periods of 10 minutes where 12:00-12:10 is 0, 12:10-12:20 is 1, 12:20-12:30=2 and so on.

Obviously if there's no data for a period the row won't exist, but you can sort that out easily in the PHP.

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 4:34 am
by VladSun
http://www.unixtimestamp.com/index.php

10 minutes = 10 *60 seconds = 600 seconds

[sql]SELECT     COUNT(DISTINCT(visit_token)),    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(visit_time)/600)*600)) AS time_stampFROM     visitsWHERE     visit_time BETWEEN '2009-06-26 00:00:00' AND '2009-06-27 00:00:00'GROUP BY     time_stampORDER BY     visit_time ASC[/sql]

While "ORDER BY visit_time" will work, it's not SQL strict.
"ORDER BY time_stamp" is enough :)

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 4:36 am
by onion2k
My version will work after 2038. :P

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 4:36 am
by VladSun
onion2k wrote:My version will work after 2038. :P
:drunk:
:lol:

Re: Trending over time with MySQL (and maybe a bit of PHP)

Posted: Fri Jul 03, 2009 3:22 pm
by Griven
:) Thank you both.