Find count during larger period grouped by smaller period - MySQL

[Hans]

Hello group,

I have the following problem:
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

Kind regards, Hans

[Gordon Burditt]

>I have the following problem:

>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

One approach to this is to take the time, divide it by the interval
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

[Hans]

"Gordon Burditt" <gordonb.omcuxburditt.org> wrote in message
news:129j116cassq962corp.supernews.com...

> >I have the following problem:
>>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

>
> One approach to this is to take the time, divide it by the interval
> 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.

I have thought of this approach, but then I only get the count() during the
interval, i.e
00:00 - 00:15
00:15 - 00:30
etc...

because
00:08 / 00:15 ( <---just to simplify ) evaluates to 0,
00:23 / 00:15 = 1,
so the record from 00:23 is not grouped into the first line of the result,
only the second.

Te rephrase my question:
Say I have the following counts
from
00:00 - 00:15 there are 8 events
00:15 - 00:30 there are 2 events
00:30 - 00:45 there are 3 events
00:45 - 01:00 there are 6 events
01:00 - 01:15 there are 5 events
01:15 - 01:30 there are 7 events
01:30 - 01:45 there are 1 events
01:45 - 02:00 there are 4 events

Then I want in my results:
00:00 - 01:00 ( 8 + 2 + 3 + 6 ) = 19 events
00:15 - 01:15 ( 2 + 3 + 6 + 5 ) = 16
00:30 - 01:30 ( 3 + 6 + 5 + 7 ) = 21
00:45 - 01:45 ( 6 + 5 + 7 + 1 ) = 19
01:00 - 02:00 ( 5 + 7 + 1 + 4 ) = 17

>
> 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).
>

All times are in UTC, so that's not an issue.

Thanks for thinking.

Gr, Hans

[Hans]

"Hans" <nieuwslezerzonnet.nl> wrote in message
news:4498ffb1$0$31644$e4fe514cnews.xs4all.nl...

> Hello group,
>
> I have the following problem:
> 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
>
> Kind regards, Hans

Anyone ???

[strawberry]

yeah, it's a tough one!

a script that is described as 'beyond excellence' is provided at
[url]http://www.thescripts.com/forum/thread80220.html[/url]

maybe it will help

Hans wrote:

> "Hans" <nieuwslezerzonnet.nl> wrote in message
> news:4498ffb1$0$31644$e4fe514cnews.xs4all.nl...

> > Hello group,
> >
> > I have the following problem:
> > 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
> >
> > Kind regards, Hans

>
> Anyone ???

[Kai Ruhnau]

Hans wrote:

> "Hans" <nieuwslezerzonnet.nl> wrote in message
> news:4498ffb1$0$31644$e4fe514cnews.xs4all.nl...

Plase only use a single line here.

>> Hello group,
>>
>> I have the following problem:
>> 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
>>
>> Kind regards, Hans

>
> Anyone ???

In principle this is not that hard, but I'm sure nobody here will
implement it that way.

The first thing you need is a query, that gives you the events grouped
by 15 minutes blocks. I name it q1 in the second part:

SELECT COUNT(*) AS quantity,
4*HOUR(`Timestamp`)+CEIL(MINUTE(`Timestamp`)/15) AS quarter
FROM Test
GROUP BY 2

Then you make a multiple self join, fetching all quarters belonging
together:

SELECT quarter1.quarter,
IFNULL(quarter1.quantity,0)+...+(IFNULL(quarter4.q uantity,0)
FROM (q1) AS quarter1
LEFT JOIN (q1) AS quarter2 ON MOD(quarter1.quarter+1,4*24)=quarter2.quarter
LEFT JOIN (q1) AS quarter3 ON MOD(quarter1.quarter+2,4*24)=quarter3.quarter
LEFT JOIN (q1) AS quarter4 ON MOD(quarter1.quarter+3,4*24)=quarter4.quarter


BUT(!)

- Every quarter has to contain at least one event. If not you have to
create a dummy table that contains all 4*24=96 quarters (=96 Entries
0-95). This quarters table is then the one used in the FROM clause.
- The query does not include day boundaries, but that can be easily
added via the definition of quarters in the first query and fixing the
second argument to MOD().
- It will return what you want, but it will not use any index and thus
will be horrible slow. When every quarter has events, the unfiltered
result set has over 80 Million records.
- It is plain ugly!

Go and do some application programming. A moving window over one query
of the type q1 will do exactly what you want.

HTH
Kai

--
This signature is left as an exercise for the reader.