I have the following query...
SELECT CreateDate, count( * )
FROM `userprofile`
GROUP BY CreateDate


It isn't exactly what I want. Records are added to this table in 2 main
ways. First people use the web site interface to create records. In
this case, records are only added by one or 2 people and with a
significant time between record inserts. The second way is through an
import that reads data from a text file. In the second case the date on
the records will all be close together with about 60 records added per
second. What I want to do is find all the groups where the inserts all
happened with in say 10 seconds. So my group by would be more like......

GROUP BY CreateDate +- 10 SECOND


Is there a way to do this?

--
Chris W
KE5GIX

"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm"

Ham Radio Repeater Database.
http://hrrdb.com

Hi,

Chris W wrote:
> I have the following query...
> SELECT CreateDate, count( * )
> FROM `userprofile`
> GROUP BY CreateDate
>
>
> It isn't exactly what I want. Records are added to this table in 2 main
> ways. First people use the web site interface to create records. In
> this case, records are only added by one or 2 people and with a
> significant time between record inserts. The second way is through an
> import that reads data from a text file. In the second case the date on
> the records will all be close together with about 60 records added per
> second. What I want to do is find all the groups where the inserts all
> happened with in say 10 seconds. So my group by would be more like......
>
> GROUP BY CreateDate +- 10 SECOND

Convert the date to a number of seconds, then round to the nearest 20
seconds and convert it back to a date. You can use FROM_UNIXTIME() and
UNIX_TIMESTAMP() for the conversion. You can round to the nearest 20
seconds by dividing by 20, rounding to the nearest whole number, and
multiplying by 20 again. Group by the resulting expression.

Baron

Chris,

What I want to do is find all the groups where the inserts all
happened with in say 10 seconds. So my group by would be more like......

Perhaps the easiest solution is to make a temp table of datetime ranges
from the resultset, then join from and group by those rowIDs.

PB

-----

Chris W wrote:
> I have the following query...
> SELECT CreateDate, count( * )
> FROM `userprofile`
> GROUP BY CreateDate
>
>
> It isn't exactly what I want. Records are added to this table in 2 main
> ways. First people use the web site interface to create records. In
> this case, records are only added by one or 2 people and with a
> significant time between record inserts. The second way is through an
> import that reads data from a text file. In the second case the date on
> the records will all be close together with about 60 records added per
> second. What I want to do is find all the groups where the inserts all
> happened with in say 10 seconds. So my group by would be more
> like......
>
> GROUP BY CreateDate +- 10 SECOND
>
>
> Is there a way to do this?
>