Re: Constructing query to display item count based on increments of time
On Wed, Oct 6, 2010 at 11:15 PM, Hank hes...@gmail.com wrote:
Ok, I can see that.
Oh, I wasn't implying that mine was necessarily better - I didn't even think
of doing it that way. I suggested a benchmark because different fuctions
might have different execution speeds. Now actually thinking about this,
I've come up with three different formulations of mine, even :-)
Given that I'm curious about this, I've bothered to do the benchmarks myself
:-) All running on the same virtual machine, a VirtualBox on my laptop,
which has 1 cpu and 384 megs of memory, running Debian 5.0.5 and MySQL
5.1.47 and using mysqlslap to perform 200.000 calls in the same thread,
iterated five times. My chosen datestamp is '2010-10-07 08:38:07'.
- select sql_no_cache unix_timestamp('2010-10-07 08:38:07') -
(unix_timestamp('2010-10-07 08:38:07') % 600);
- Average number of seconds to run all queries: 30.592 seconds
Minimum number of seconds to run all queries: 30.084 seconds
Maximum number of seconds to run all queries: 31.374 seconds
- select sql_no_cache (unix_timestamp('2010-10-07 08:38:07') div 600) *
600;
- Average number of seconds to run all queries: 29.606 seconds
Minimum number of seconds to run all queries: 29.454 seconds
Maximum number of seconds to run all queries: 29.813 seconds
- select sql_no_cache floor(unix_timestamp('2010-10-07 08:38:07') / 600)
* 600;
- Average number of seconds to run all queries: 30.857 seconds
Minimum number of seconds to run all queries: 30.274 seconds
Maximum number of seconds to run all queries: 31.378 seconds
- select sql_no_cache concat(date_format('2010-10-07 08:38:07', '%Y-%m-%d
%H:'), truncate(minute('2010-10-07 08:38:07') / 10, 0), '0');
- Average number of seconds to run all queries: 32.905 seconds
Minimum number of seconds to run all queries: 32.099 seconds
Maximum number of seconds to run all queries: 33.429 seconds
- select sql_no_cache concat(left(DATE_FORMAT('2010-10-07 08:38:07',
'%Y-%m-%d %h:%i'),15),'0');
- Average number of seconds to run all queries: 31.042 seconds
Minimum number of seconds to run all queries: 30.369 seconds
Maximum number of seconds to run all queries: 31.727 seconds
- select sql_no_cache DATE_FORMAT('2010-10-07 08:38:07', '%Y-%m-%d %h:'
), 10*(minute('2010-10-07 08:38:07')%6);
- Average number of seconds to run all queries: 32.012 seconds
Minimum number of seconds to run all queries: 31.335 seconds
Maximum number of seconds to run all queries: 32.894 seconds
So, it turns out that the method used doesn't make a major difference.
My guess would be that function execution and arithmetic is actually pretty
much instant, and that the major cost here was simply initializing the
parser and other structures. There is in any case no obvious relation
between number of function calls and execution time here.
Here's a different approach that gets it down to
two function calls and some math.. and the DATE_FORMAT call might not
even be needed depending on the actual application.
select
DATE_FORMAT(start_time, %Y-%m-%d %h: ) as dhour,
10*(minute(start_time)%6) as dtime ,count(*)
from table
group by dhour,dtime;
-Hank
On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman vegiv...@tuxera.be
wrote:
Two people already who suggested a text-based approach vs. my numeric
approach.
Analysing, my method takes a single function call per record
(to_unixtime);
Travis' takes 4 (concat, date_format, truncate, minute) and Hank's 3
(concate, left, date_format).
Someone feel like benchmarking ? :-D
On Wed, Oct 6, 2010 at 5:44 PM, Hank hes...@gmail.com wrote:
Here's what I came up with:
select concat(left(DATE_FORMAT(start_time, %Y-%m-%d %h:%i
),15),0) as time, count(*) from table group by time
-Hank
How would one go about to construct a query that counts items within
an
increment or span of time, let's say increments of 10 minutes?
Imagine a simple table where each row has a timestamp, and the query
should return the count of items occurring within the timespan of a
defined
period.
Say,
09:00: 14
09:10: 31
09:20: 25
09:30: 0
09:40: 12
etc.
I have been able to get collections of item occurrence based on month
and
day by using GROUP BY together with a DATE_FORMAT( start_time, %d %m
%Y )
eg.
I can however not seem to be able to find the solution to grouping
based
on the minute increments in my example above.
Any suggestions?
--
Kind regards
Pascual Strømsnæs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com
--
MySQL General Mailing List
For list archives:
Constructing query to display item count based on increments of time
Hi! How would one go about to construct a query that counts items within an increment or span of time, let's say increments of 10 minutes? Imagine a simple table where each row has a timestamp, and the query should return the count of items occurring within the timespan of a defined period. Say, 09:00: 14 09:10: 31 09:20: 25 09:30: 0 09:40: 12 etc. I have been able to get collections of item occurrence based on month and day by using GROUP BY together with a DATE_FORMAT( start_time, %d %m %Y ) eg. I can however not seem to be able to find the solution to grouping based on the minute increments in my example above. Any suggestions? -- Kind regards Pascual Strømsnæs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Constructing query to display item count based on increments of time
convert to unixtime, convert your interval to unixtime, creatively combine with integer division to get a base number for each period, group by that and count(). 2010/10/6 Pascual Strømsnæs pasc...@egoria.no Hi! How would one go about to construct a query that counts items within an increment or span of time, let's say increments of 10 minutes? Imagine a simple table where each row has a timestamp, and the query should return the count of items occurring within the timespan of a defined period. Say, 09:00: 14 09:10: 31 09:20: 25 09:30: 0 09:40: 12 etc. I have been able to get collections of item occurrence based on month and day by using GROUP BY together with a DATE_FORMAT( start_time, %d %m %Y ) eg. I can however not seem to be able to find the solution to grouping based on the minute increments in my example above. Any suggestions? -- Kind regards Pascual Strømsnæs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Constructing query to display item count based on increments of time
Maybe you could use something like the following to truncate your times to 10 minute increments before doing your GROUP BY and COUNT(): select concat(date_format(timestamp_col, '%Y-%m-%d %H:'), truncate(minute(timestamp_col) / 10, 0), '0') from your_table; -Travis -- From: Pascual Strømsnæs pasc...@egoria.no Sent: Wednesday, October 06, 2010 4:20 AM To: [MySQL] mysql@lists.mysql.com Subject: Constructing query to display item count based on increments of time Hi! How would one go about to construct a query that counts items within an increment or span of time, let's say increments of 10 minutes? Imagine a simple table where each row has a timestamp, and the query should return the count of items occurring within the timespan of a defined period. Say, 09:00: 14 09:10: 31 09:20: 25 09:30: 0 09:40: 12 etc. I have been able to get collections of item occurrence based on month and day by using GROUP BY together with a DATE_FORMAT( start_time, %d %m %Y ) eg. I can however not seem to be able to find the solution to grouping based on the minute increments in my example above. Any suggestions? -- Kind regards Pascual Strømsnæs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Constructing query to display item count based on increments of time
Here's what I came up with: select concat(left(DATE_FORMAT(start_time, %Y-%m-%d %h:%i ),15),0) as time, count(*) from table group by time -Hank How would one go about to construct a query that counts items within an increment or span of time, let's say increments of 10 minutes? Imagine a simple table where each row has a timestamp, and the query should return the count of items occurring within the timespan of a defined period. Say, 09:00: 14 09:10: 31 09:20: 25 09:30: 0 09:40: 12 etc. I have been able to get collections of item occurrence based on month and day by using GROUP BY together with a DATE_FORMAT( start_time, %d %m %Y ) eg. I can however not seem to be able to find the solution to grouping based on the minute increments in my example above. Any suggestions? -- Kind regards Pascual Strømsnæs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=hes...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Constructing query to display item count based on increments of time
Two people already who suggested a text-based approach vs. my numeric approach. Analysing, my method takes a single function call per record (to_unixtime); Travis' takes 4 (concat, date_format, truncate, minute) and Hank's 3 (concate, left, date_format). Someone feel like benchmarking ? :-D On Wed, Oct 6, 2010 at 5:44 PM, Hank hes...@gmail.com wrote: Here's what I came up with: select concat(left(DATE_FORMAT(start_time, %Y-%m-%d %h:%i ),15),0) as time, count(*) from table group by time -Hank How would one go about to construct a query that counts items within an increment or span of time, let's say increments of 10 minutes? Imagine a simple table where each row has a timestamp, and the query should return the count of items occurring within the timespan of a defined period. Say, 09:00: 14 09:10: 31 09:20: 25 09:30: 0 09:40: 12 etc. I have been able to get collections of item occurrence based on month and day by using GROUP BY together with a DATE_FORMAT( start_time, %d %m %Y ) eg. I can however not seem to be able to find the solution to grouping based on the minute increments in my example above. Any suggestions? -- Kind regards Pascual Strømsnæs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Constructing query to display item count based on increments of time
Ok, I can see that. Here's a different approach that gets it down to two function calls and some math.. and the DATE_FORMAT call might not even be needed depending on the actual application. select DATE_FORMAT(start_time, %Y-%m-%d %h: ) as dhour, 10*(minute(start_time)%6) as dtime ,count(*) from table group by dhour,dtime; -Hank On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman vegiv...@tuxera.be wrote: Two people already who suggested a text-based approach vs. my numeric approach. Analysing, my method takes a single function call per record (to_unixtime); Travis' takes 4 (concat, date_format, truncate, minute) and Hank's 3 (concate, left, date_format). Someone feel like benchmarking ? :-D On Wed, Oct 6, 2010 at 5:44 PM, Hank hes...@gmail.com wrote: Here's what I came up with: select concat(left(DATE_FORMAT(start_time, %Y-%m-%d %h:%i ),15),0) as time, count(*) from table group by time -Hank How would one go about to construct a query that counts items within an increment or span of time, let's say increments of 10 minutes? Imagine a simple table where each row has a timestamp, and the query should return the count of items occurring within the timespan of a defined period. Say, 09:00: 14 09:10: 31 09:20: 25 09:30: 0 09:40: 12 etc. I have been able to get collections of item occurrence based on month and day by using GROUP BY together with a DATE_FORMAT( start_time, %d %m %Y ) eg. I can however not seem to be able to find the solution to grouping based on the minute increments in my example above. Any suggestions? -- Kind regards Pascual Strømsnæs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=hes...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
