On Wed, Oct 6, 2010 at 11:15 PM, Hank 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
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
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
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?
>>
our_table;
-Travis
--
From: "Pascual Strømsnæs"
Sent: Wednesday, October 06, 2010 4:20 AM
To: "[MySQL]"
Subject: Constructing query to display item count based on increments of
time
Hi!
How would one go about to construct a query that counts it
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
> Hi!
>
> How would one go about to construct a query that counts items within an
> increment or span of t
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.
