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

BananaHead · January 14, 2009, 11:59am

Hello all, I have a sql statement that returns a count for every hour on my db, how do I change this so it returns a count for every 15 minutes of the hour?



SELECT date_format( timestamp, '%h:00 %p' ) AS HOUR , count( * ) AS mycount
FROM adsense_log
WHERE timestamp LIKE '%2009-01-13%'
GROUP BY DATE( timestamp ) , hour( timestamp )
LIMIT 0 , 30

I setup the db so there is a record at each 15 minute interval from 2009-01-13 01:00:00 to 2009-01-13 05:00:00 (total of 20 records).

I have tried the following as well:

SELECT
DATE_FORMAT(
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp)/(15*60))*15*60)
, '%Y-%m-%d %h:%m %p') AS HOUR, COUNT(*) AS mycount
FROM adsense_log
WHERE Timestamp BETWEEN  '2009-01-13 00:00:00' AND '2009-01-13 23:59:59'
GROUP BY HOUR LIMIT 0,30;

But still groups the count, the resultset still is:

HOUR mycount
2009-01-13 01:01 AM 4
2009-01-13 02:01 AM 4
2009-01-13 03:01 AM 4
2009-01-13 04:01 AM 4
2009-01-13 05:01 AM 4

Someone told me I need to use a modulus for every 15 minutes, but I have no idea how to put it together. Can anyone help?

Thanks!

r937 · January 14, 2009, 12:09pm

your TIMESTAMP column has a very unfortunate name, as TIMESTAMP is a reserved word

what datatype is it? could you do a SHOW CREATE TABLE for your table?

modulus is wrong in this instance, better is rounding using FLOOR

but the part i really don’t understand is… if you create a row every 15 minutes, why do you need to GROUP BY on 15 minute intervals? just return the data!!

BananaHead · January 14, 2009, 12:23pm

CREATE TABLE `adsense_log` (
 `impressions` varchar(50) collate utf8_unicode_ci NOT NULL,
 `clicks` varchar(50) collate utf8_unicode_ci NOT NULL,
 `ctr` varchar(50) collate utf8_unicode_ci NOT NULL,
 `ecpm` varchar(50) collate utf8_unicode_ci NOT NULL,
 `earnings` varchar(50) collate utf8_unicode_ci NOT NULL,
 `timestamp` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Datatype is DATETIME

On my case, rows are created around the clock, I need to be able to see count the total records every 15minutes so i can display them on a chart that shows hits every 15 minutes

Thanks!

r937 · January 14, 2009, 12:43pm

i would strongly suggest renaming the column

SELECT FROM_UNIXTIME(
         CEILING(UNIX_TIMESTAMP(`timestamp`)/900)*900
                    ) AS timeslice
     , COUNT(*) AS mycount
  FROM adsense_log
 WHERE `timestamp` &gt;= '2009-01-13'
   AND `timestamp`  &lt; '2009-01-14'
GROUP 
    BY timeslice

900, of course, is 60*15 (60 seconds * 15 minutes)

notice also the WHERE clause, which is the most efficient way of filtering datetimes

BananaHead · January 14, 2009, 12:53pm

r937, That actually worked to some extent, it is returning me the count for each 15minute, anything created in-between (say 01:12:00) doesnt get counted.

Say i have a record that was created at 2009-01-04 01:00:00 and one at 2009-01-04 01:10:00 now I have another that was created at 2009-01-04 01:15:00, the result set should be 2 records created between 01:00:00 and 01:15:00 and one between 01:15:00 and 01:30:00
So between the first 15mins of the hour, 2 records were created, and between the 15th and 30th of the hour, 1

Did I explain better? ??? ???

r937 · January 14, 2009, 1:00pm

try substituting FLOOR for CEILING

BananaHead · January 14, 2009, 1:08pm

That worked!!! Thank you r937, being trying to get that done for 2 days! Really Appreciate!

djones · January 14, 2009, 6:37pm

Rudy is indeed an SQL/Database Guru!

Sorry to go off-topic, but what’s happening with “Simply SQL”? Amazon.com are listing it, but I don’t see anything on SitePoint???

r937 · January 14, 2009, 6:44pm

sitepoint will not announce it for a while yet, it was supposed to be announced in december but the latest word is, maybe february

not much i can do about it

elgumbo · January 19, 2009, 11:08am

Just checked and the book is now listed on sitepoint: http://www.sitepoint.com/books/sql1

r937 · January 19, 2009, 1:28pm

yep, just listed yesterday

everybody start buying!!!

guelphdad · January 19, 2009, 3:12pm

Got mine last week.

westcoastsql · February 12, 2011, 5:41pm

hello,

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

that line where there are no records during a timeslice isnt displayed. how do i get the query to include zero counts?

thanks

r937 · February 12, 2011, 6:11pm

you need to use a LEFT OUTER JOIN

the left table will contain all the time slices, while the right table will contain your data – and the LEFT OUTER JOIN will ensure that all time slices are in the result set, with or without any matching data

so where does this left table of time slices come from?

you can generate it easily with a numbers table –


CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
INSERT INTO numbers ( n ) VALUES 
 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),... ;

you can then generate the time slices like this –


SELECT '2009-01-13 17:00:00' -- start
       + INTERVAL n*15 MINUTE 
          AS timeslice
  FROM numbers
 WHERE '2009-01-13 17:00:00' 
       + INTERVAL n*15 MINUTE
    <= '2009-01-14 00:00:00' -- end

test this by itself to see that it works

then all you have to do is add your LEFT OUTER JOIN

olivier89 · June 22, 2011, 12:45pm

Hello everyone, and r937

Sorry for my dumbness, but although your precise explanations for the last post i can’t get the parts together :

SELECT FROM_UNIXTIME(
	FLOOR(UNIX_TIMESTAMP(`timestamp`)/900)*900
	) AS timeslice
	, COUNT(*) AS mycount, sliced.*
	FROM
		(SELECT '2009-01-13 17:00:00'
			+ INTERVAL n*15 MINUTE
				AS timeslice
			FROM numbers
			WHERE '2009-01-13 17:00:00'
			+ INTERVAL n*15 MINUTE
			&lt;= '2009-01-14 00:00:00') AS sliced
	LEFT OUTER JOIN adsense_log AS L
	ON sliced.timeslice = L.`timeslice`
	WHERE L.`timeslice` &gt;= '2009-01-13'
	AND L.`timeslice`  &lt; '2009-01-14'
GROUP
	BY timestamp

doesn’t get me the results

The subselect works well and brings the timeslice, the other part brings alls the results, but not the Left outer join :
Once again, your code is obviously right, but could you give the final left outer join request.

I’m ashamed, but in need

Thanks

DarthGuido · June 22, 2011, 12:56pm

Change the WHERE conditions in


WHERE [B][COLOR="Red"]sliced[/COLOR][/B].`timeslice` &gt;= '2009-01-13'
AND [B][COLOR="red"]sliced[/COLOR][/B].`timeslice`  &lt; '2009-01-14'

Putting where conditions on the left joined table makes the left join act as an INNER JOIN.

olivier89 · June 22, 2011, 3:38pm

In fact, even if i change to

SELECT FROM_UNIXTIME(
	FLOOR(UNIX_TIMESTAMP(`timestamp`)/900)*900
	) AS timeslice
	, COUNT(*) AS mycount, sliced.* 
	FROM 
		(SELECT '2009-01-13 17:00:00' 
			+ INTERVAL n*15 MINUTE 
				AS timeslice
			FROM numbers
			WHERE '2009-01-13 17:00:00' 
			+ INTERVAL n*15 MINUTE
			&lt;= '2009-01-14 00:00:00') AS sliced
	LEFT OUTER JOIN adsense_log AS L 
	ON sliced.timeslice = L.`timeslice`
	WHERE [COLOR="Red"]sliced[/COLOR].`timeslice` &gt;= '2009-01-13'
	AND [COLOR="Red"]sliced[/COLOR].`timeslice`  &lt; '2009-01-14'
GROUP 
	BY timestamp

i got an error :

Unknown column 'L.`timeslice`' in 'on clause'

but if i change :

ON sliced.timeslice = L.`timeslice`

to

ON sliced.timeslice = L.`timestamp`

i won’t get the proper results : i’ll have 1 line for all the Null results.
Maybe i’m confused with the the two post giving the answer :
in each, we got a timeslice

SELECT FROM_UNIXTIME(
CEILING(UNIX_TIMESTAMP(timestamp)/900)900
) AS timeslice
, COUNT() AS mycount
FROM adsense_log
WHERE timestamp >= ‘2009-01-13’
AND timestamp < ‘2009-01-14’
GROUP
BY timeslice

and

SELECT ‘2009-01-13 17:00:00’
+ INTERVAL n15 MINUTE
AS timeslice
FROM numbers
WHERE ‘2009-01-13 17:00:00’
+ INTERVAL n15 MINUTE
<= ‘2009-01-14 00:00:00’

I’m really not getting it

r937 · June 22, 2011, 4:55pm

why are you running somebody else’s query from two years ago?

olivier89 · June 22, 2011, 5:56pm

Because, i didn’t want to mess with the original post and use my own data stucture…
So, of course i don’t use these names, but i aim the same goal :
return a group count for every x minutes (15 is ok, i think i could change the time for my needs), AND that the results show no holes if no records : having a 0 value for those.

If you prefer i could post a new thread with the same question but with my content, i don’t mind.
But i can also give you my structure :
My table with the datetime

CREATE TABLE IF NOT EXISTS `tracking` (
  `id_track` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `id_liste_track` mediumint(8) unsigned NOT NULL,
  `date_track` datetime NOT NULL,
  PRIMARY KEY (`id_track`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

i use your table structure for numbers

CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
INSERT INTO numbers ( n ) VALUES 
 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),... ;

AND my sample data :

INSERT INTO tracking (id_track, id_liste_track, date_track) VALUES
(1, 4, ‘2011-06-21 18:31:14’),
(2, 4, ‘2011-06-20 20:01:01’),
(3, 4, ‘2011-06-21 10:36:15’),
(4, 4, ‘2011-06-21 16:30:47’),
(5, 4, ‘2011-06-21 18:39:14’);

and the outer join request not giving me satisafation :

SELECT FROM_UNIXTIME(
	CEILING(UNIX_TIMESTAMP(`date_track`)/900)*900
) AS `thedate`, 
count(*) AS mycount, 
`sliced`.`timeslice`
FROM (
	SELECT '2011-06-20 20:01:01'
	+ INTERVAL n*15 MINUTE
	AS `timeslice`
	FROM `numbers`
	WHERE '2011-06-20 20:01:01'
	+ INTERVAL n*15 MINUTE
	<= '2011-06-22 19:01:01'
	) 
	AS `sliced` 
LEFT OUTER JOIN `tracking` AS NT 
	ON `sliced`.`timeslice` = NT.`date_track`
WHERE `sliced`.`timeslice` >= '2011-06-20 20:01:01' 
AND `sliced`.`timeslice` < '2011-06-22 20:01:01' 
GROUP BY `thedate`

i’m having this :
thedate | mycount | timeslice
NULL | 188 | 2011-06-20 20:16:01
2011-06-20 20:15:00 | 1 | 2011-06-20 20:01:01

But i’d like to have something like that, given the data provided :
thedate | mycount | timeslice
2011-06-20 20:15:00 | 1 | 2011-06-20 20:01:01
NULL | 0 | 2011-06-20 20:16:01
NULL | 0 | 2011-06-20 20:31:01
…
NULL | 0 | 2011-06-21 10:16:01
2011-06-21 10:36:15 | 1 | 2011-06-21 10:31:01
…
NULL | 0 | 2011-06-21 18:16:01
2011-06-21 18:31:14 | 2 | 2011-06-21 18:31:01

r937 · June 22, 2011, 6:09pm

no problem, you have a similar issue, so let’s do it here

you’ve given your table layout and sample data, which is great

but i wonder if i could ask you to state, in words, what it is the query is supposed to be doing, and where those NULLs are supposed to come from…