>I have the following problem:One approach to this is to take the time, divide it by the interval>The table I use for recording events is this:
>
>CREATE TABLE `Test` (
> `Timestamp` timestamp NOT NULL,
> `Source` varchar(20) default NULL,
> `Dest` varchar(20) default NULL,
> `Event` varchar(20) default NULL,
> `Data` blob,
> PRIMARY KEY (`Timestamp`)
>)
>
>I want to find how many events happened during one hour, starting every 15
>minutes, thus:
>
>00:00 - 01:00 34 events
>00:15 - 01:15 43 events
>00:30 - 01:30 22 events
>..
>..
>23:00 - 00:00 26 events
>23:15 - 00:15 29 events
>23:30 - 00:30 36 events
>23:45 - 00:45 28 events
>
>Any hints pointing me in a direction toward any solution is highly
>appreciated
involved, truncate it to an integer, and then GROUP BY this value.
Then you can use count() to find the number of rows grouped.
You might want to multiply it back so the value you're
GROUP BYing also represents the start of the interval.
from_unixtime(truncate(unix_timestamp(event_time)/900, 0))
might be appropriate if event_time falls within the range representable
by a UNIX timestamp and all daylight savings time shifts are multiples
of 15 minutes (not true, I believe, of Saudi Arabian solar time).
If you are using an appropriate interval, like 1 month, 1 day, 1
hour, 10 minutes, 1 minute, etc., you can just take a date stamp
string and lop off the portions of the string that don't count with
left(), then GROUP BY that.
YYYY-MM-DD HH:MM:SS
_______________X
left(event_time, 15) gives you 10's of minutes and up, but
this approach won't work for 15 minutes. Depending on your version
of MySQL, the timestamp may have a different format than a datetime,
and the length kept may have to change accordingly.
Gordon L. Burditt
Bookmarks