Trending over time with MySQL (and maybe a bit of PHP)
Moderator: General Moderators
Trending over time with MySQL (and maybe a bit of PHP)
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.
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)
[ 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'm naughty, are you naughty?'>smurf</span> removed
Perform a resampling to 10 minutes and GROUP BY this value.
EDIT: <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> removed
Last edited by VladSun on Fri Jul 03, 2009 2:10 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Trending over time with MySQL (and maybe a bit of PHP)
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.
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?
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'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)
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.
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?
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'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)
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.
[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.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Trending over time with MySQL (and maybe a bit of PHP)
Thanks for the prompt followup!
Based on your suggestion, this is what I came up with:
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.
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 ASCDo 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)
[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.
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)
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
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
Last edited by VladSun on Fri Jul 03, 2009 4:39 am, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Trending over time with MySQL (and maybe a bit of PHP)
My version will work after 2038. 
Re: Trending over time with MySQL (and maybe a bit of PHP)
onion2k wrote:My version will work after 2038.
There are 10 types of people in this world, those who understand binary and those who don't