How do I return a group count for every 15 minutes of the hour on my Table?

olivier89 · June 22, 2011, 7:23pm

To begin with, you’re right, i not really sure what i’m writing i’m a mysql (or sql) newbie.

But, with the data i’ve given, the request really returned in phpmyadmin

thedate | mycount | timeslice
NULL | 188 | 2011-06-20 20:16:01
2011-06-20 20:15:00 | 1 | 2011-06-20 20:01:01

(the numbers table is filled with numbers from 1 to 1000)
please, don’t ask me for the 188 …, i would only wild guess about it
(and of course i really don’t have a clue why i’m having those results…)

And that’s why i “supposed”, guessing data results, i put those i’ve written
But, you can forget those if they’re irrelevant. In fact, i don’t need them, i just need the count and the interval.

So, to answer your question, i was thinking those NULL were coming from the interval in which there are no data in my table tracking (i thought 0 record in the outer join would bring a NULL)

so in nearly “human language” it would like to have :
between 2011-06-20 20:01:01 (included) and 2011-06-20 20:16:01 (not incuded) => 1 result
between 2011-06-20 20:16:01 (included) and 2011-06-20 20:31:01 (not incuded) => 0 result
between 2011-06-20 20:31:01 (included) and 2011-06-20 20:46:01 (not incuded) => 0 result
…
between 2011-06-21 10:16:01 (included) and 2011-06-21 10:31:01 (not incuded) => 0 result
between 2011-06-21 10:31:01 (included) and 2011-06-21 10:46:01 (not incuded) => 1 result
…
between 2011-06-21 18:16:01 (included) and 2011-06-21 18:31:01 (not incuded) => 0 result
between 2011-06-21 10:31:01 (included) and 2011-06-21 10:46:01 (not incuded) => 2 results

Anyway, thank you for the time you give.

r937 · June 22, 2011, 7:36pm

sorry, i still don’t understand what you’re doing

obviously, you want to match some data from your table to some data generated by intervals, otherwise you wouldn’t’ve ended up in this thread

but can you explain what you want in words, without trying to show the results using data?

olivier89 · June 22, 2011, 8:21pm

Sorry again if i’m not clear.
I have data input recorded in the table ‘tracking’. each record has different dates. but dates are not at the same interval.
Like the example i gave, the date in recordset are not at the same interval.
I want to group the result by interval of x minutes (15 for instance)
so, for ALL the records, i want to have for every 15 minute interval, the number of records in the table ‘tracking’ matching this range.

I don’t really know how to make it clearer.

r937 · June 22, 2011, 8:37pm

now we be getting someplace

so all you want is a count?

how do you want to determine the starting point and ending point of the intervals?

olivier89 · June 22, 2011, 9:00pm

someplace is a good place to start.

To answer your questions :

for my present purpose, yes, “all” i need is the count for each range (0 if none, the number of recordset within the range if any match)
Well, i’d like to have the count of records of each interval within the 15 minute range,
FROM the oldest one (MIN(date_track) ?)
TO the most recent (MAX(date_track) ?)… (or NOW(), i don’t think this would change the query very much)

Sorry to be confused, but i really thought i could start from your example from this post to achieve my goal.

r937 · June 22, 2011, 11:06pm

okay, let’s build your query up in stages

please test this –

SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
  FROM ( SELECT MIN(date_track) AS min_date
              , MAX(date_track) AS max_date
           FROM tracking ) AS m
CROSS
  JOIN numbers
 WHERE min_date + INTERVAL n*15 MINUTE <= max_date

let me know if this gives the intervals you want

olivier89 · June 23, 2011, 7:04am

That’s all ok, it gives me allright a 15 minutes interval between the oldest 2011-06-20 20:01:01 and the last 2011-06-21 18:39:14 =>( interval 2011-06-21 18:31:01)
and i could even change MAX(date_track) with NOW() if i need it.

So it’s all for the best

i’m just wondering why this request doesn’t looks like the one you suggested for westcoastsql in his question because, to me it was the same need, hence my intrusion in this post …

i know this thread is old, but i have a question for r937. That sql query is very good, but what if there is gaps longer then 15mins in the data?

i need the following to be shown:

date mycount
2009-01-13 17:00:00 5
2009-01-13 17:15:00 3
2009-01-13 17:30:00 0 <- this doesnt show
2009-01-13 17:45:00 8

Anyway, i’ll stick to my request (er …yours ) and looking forward to see it giving the count, because if i dumbly add a count(*) in the select, it’s not the right right thing to do…

r937 · June 23, 2011, 10:57am

okay, so that query gives you the intervals

you are correct that you can vary the contents of the “m” subquery, like using NOW() instead of MAX(date_track)

in fact you don’t even need the tracking table, you can use constants like this –

SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
  FROM ( SELECT DATE('2011-06-01') AS min_date
              , DATE('2011-06-30') AS max_date ) AS m
CROSS
  JOIN numbers
 WHERE min_date + INTERVAL n*15 MINUTE <= max_date

in any case, we are now ready to do the LEFT OUTER JOIN –

SELECT d.timeslice
     , COUNT(t.id_track)
  FROM ( SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
           FROM ( SELECT DATE('2011-06-01') AS min_date
                       , DATE('2011-06-30') AS max_date ) AS m
         CROSS
           JOIN numbers
          WHERE min_date + INTERVAL n*15 MINUTE <= max_date
       ) AS d
LEFT OUTER
  JOIN tracking AS t
    ON t.date_track BETWEEN d.timeslice
                        AND d.timeslice + INTERVAL 15 MINUTE
GROUP
    BY d.timeslice

olivier89 · June 23, 2011, 11:27am

o my ! You just made my day
This is exactly what i expected. My main question is now answered, and i thank you a thousand times for it.

But, just for my enlightment, could you explain me or rather show me a way to find an explanation about the n* in INTERVAL n*15 part in the query :

i’ve found tons of links about INTERVAL in mysql (used in DATE OR DATETIME)
INTERVAL 15 MINUTES
But where does this n comes from ? is it a standard variable name (or could it be “inc”, “step”…) ?
You don’t have to explain to me, but if only you could tell me what to google to find answer about it.

Anyway, thanks again and again

r937 · June 23, 2011, 2:38pm

from the numbers table, remember?

CREATE TABLE [B][COLOR="red"]numbers[/COLOR][/B] ( [B][COLOR="Red"]n[/COLOR][/B] INTEGER NOT NULL PRIMARY KEY );
INSERT INTO [B][COLOR="red"]numbers[/COLOR][/B] ( [B][COLOR="red"]n[/COLOR][/B] ) VALUES 
 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),... ;

SELECT min_date + INTERVAL [B][COLOR="red"]n[/COLOR][/B]*15 MINUTE AS timeslice
  FROM ( SELECT MIN(date_track) AS min_date
              , MAX(date_track) AS max_date
           FROM tracking ) AS m
CROSS
  JOIN [B][COLOR="red"]numbers[/COLOR][/B]
 WHERE min_date + INTERVAL [B][COLOR="red"]n[/COLOR][/B]*15 MINUTE <= max_date

olivier89 · June 23, 2011, 4:15pm

Oooh just forget this silly question

but i have another one which i hope is not silly :

Each list (with an id_liste_track) has some ‘date_track’ values in ‘tracking’ table as i gave an example of.
i’d like to add a WHERE clause to restrict the search to an ‘id_liste_track’.
I naively added it to your select, but it returned then only the ‘timeslice’ associated with non null count. => it seems it “broke” the left outer join effect.

SELECT d.timeslice
     , COUNT(t.id_track)
  FROM ( SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
           FROM ( SELECT DATE('2011-06-01') AS min_date
                       , DATE('2011-06-30') AS max_date ) AS m
         CROSS
           JOIN numbers
          WHERE min_date + INTERVAL n*15 MINUTE <= max_date
       ) AS d
LEFT OUTER
  JOIN tracking AS t
    ON t.date_track BETWEEN d.timeslice
                        AND d.timeslice + INTERVAL 15 MINUTE
[COLOR="Red"]WHERE t.id_liste_track = 6 [/COLOR]
GROUP
    BY d.timeslice

r937 · June 23, 2011, 4:38pm

that’s actually a good way to describe it – the WHERE clause “broke” the left outer join

change the word WHERE to AND and watch what happens

olivier89 · June 23, 2011, 4:51pm

Well, it works now. Why should i doubt you

SQL is pretty confusing, or maybe i’m such a newbie i can’t figure this myself : i use WHERE to restrict a request, and i use AND to add a restriction to something (a WHERE for instance). So it means here, i should use a AND :

because of the outer join makes the WHERE useless or irrelevant ?
because there’s already a AND (AND d.timeslice + INTERVAL 15 MINUTE ) ?

Anyway, i keep asking cause you’re a great teacher, and because i like to understand what i’m taught. (Maybe one day, i’ll have to reuse this in another context…)

r937 · June 23, 2011, 7:45pm

that is correct

you can add a restriction in three places – the WHERE clause, the HAVING clause, and the ON clause of the join

that’s what it requires here – if you want to count date_track values for only a specific id_liste_track, this is a join condition, so it goes into the ON clause

olivier89 · June 23, 2011, 8:09pm

Well, for the rest of my last needs concerning this request (another join) i won’t overload this topic, i’ll take the time to search for myself :find: or i’ll eventually open another discussion. Thnak you for everything.