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.
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
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
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
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
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
Thanks Gavin and Joerg, that was very helpful!
-- Jonas
On Sun, Oct 3, 2010 at 12:44 PM, Joerg Bruehe joerg.bru...@oracle.comwrote:
Hi Neil, all!
Tompkins Neil wrote:
So if you have individual indexes for example field_1, field_2 and
field_3
etc and then perform a search like
WHERE
Simcha Younger wrote:
On Mon, 04 Oct 2010 16:11:08 -0400
kalin m ka...@el.net wrote:
what i'm trying to do is update the column only of one of those times
isn't yet passed. and it works. except sometimes...
like these 2 unix times:
this was in the table under unix time:
I recently upgraded mysql from 5.1.9-beta to 5.1.50
Since I used the custom data dir path, my upgrade is basically a replacement
of the bin folder and I used the old my.ini file that pointing the data dir
to the right path.
Then I found, in the event viewer, two warnings:
(1)
