Counting problem

I have search date form, and I need to count the active user whose start_time and end_time between with the 1 hour interval, the time is from 1:00 am - 2:00 am ,3:00 am - 4:00 am etc…

for example: 1:00-2:00 am
I need to count all the users whose start_time and end_time between 1:00 and 2:00 .

time 3:00 - 4:00
I need to count also the user whose start_time and end_time between 3:00 and 4:00 .

and so on…

here is the expected output.

+----------------+------------+------------+------------+------------+------------+
|      Time      | 2015-09-07 | 2015-09-08 | 2015-09-09 | 2015-09-10 | 2015-09-11 |
+----------------+------------+------------+------------+------------+------------+
| 1:00-2:00 am   |          1 |          2 |         15 |          7 |          1 |
| 3:00-4:00 am   |          2 |          5 |          2 |          2 |          1 |
| 5:00-6:00 am   |          5 |          3 |          7 |          1 |          1 |
| 7:00-8:00 am   |          6 |          8 |          3 |          2 |          1 |
| 9:00-10:00 am  |          1 |          1 |          2 |          2 |          2 |
| 11:00-12:00 nn |          4 |          0 |          5 |          3 |          6 |
| 13:00-14:00 pm |          0 |          0 |          1 |          2 |          7 |
| 15:00-16:00 pm |          2 |          2 |          7 |          5 |          7 |
| 17:00-18:00 pm |          1 |          6 |          2 |          1 |          1 |
| 19:00-20:00 pm |          5 |          3 |          1 |          1 |          1 |
| 21:00-22:00 pm |          5 |          2 |          2 |          1 |          2 |
| 23:00-00:00 mn |          6 |          1 |          2 |          1 |          5 |
+----------------+------------+------------+------------+------------+------------+

Is this possible ?

CREATE TABLE `activelogs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `start_time` DATETIME NOT NULL,
    `end_time` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `start_time` (`start_time`, `end_time`)
    INDEX `user_id` (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Thank you in advance.

I see only one column for “Time”, maybe you could have in your database “Start Time” and “End Time” columns

Just my suggestion

[quote=“cssbonding, post:2, topic:205779, full:true”]maybe you could have in your database “Start Time” and “End Time” columns[/quote]look more closely, they are there :slight_smile:

jemz, what about a user whose start_time is between 1:00 am - 2:00 am, but end_time between 3:00 am - 4:00 am? where would you count that?

Hi!

This is a problem of a Top-N Query.

in the start_time. in the 1:00-2:00 am

I hope I understand your question correctly. Depending on the database engine you are using you can use the BETWEEN filter (make sure you cast your DATETIMEs correctly).

You could also select a date range, and GROUP BY HOUR($date).

sweet

two final questions –

which database system are you using?

which application language are you using?

Mysql and Php .

Thank you in advance.

A problem you have is that you want to the date in the timestamp as the column name. You cannot achieve this in pure MySQL without using what’s called Table Pivoting . A simpler approach is to group hours by distinct user id and date, process that using PHP and pivot your data that way. That said -

SELECT 
	DATE(l.start_time) AS `Day`, 
	CONCAT_WS(' - ', HOUR(l.start_time), HOUR(l.start_time) + 1) AS `Hour`,
	COUNT(DISTINCT(l.user_id)) as `Users`
FROM activelogs l
WHERE (
	(DATEDIFF(l.start_time, l.end_time) = 0) AND
	(HOUR(l.start_time) = HOUR(l.end_time))
)
GROUP BY `Day`,`Hour`; 

The above query should mostly do what you want. You can fiddle with MySQL’s date formatting functions to make it more the way you want.

Good luck!

since you have a numbers table already (How to count number of dates base on usage - #17 by r937), we can use it here to get all the hourly intervals

you could theoretically also apply the same technique to span your range of dates, but for now i’ll just simply use all dates in the table

SELECT dates.thedate , CONCAT(RIGHT(CONCAT('0',numbers.n,':00'),5) ,' - ' ,RIGHT(CONCAT('0',numbers.n+1,':00'),5)) AS time_slot , COUNT(activelogs.start_time) AS thecount FROM numbers CROSS JOIN ( SELECT DISTINCT DATE(start_time) AS thedate FROM activelogs ) AS dates LEFT OUTER JOIN activelogs ON DATE(activelogs.start_time) = dates.thedate AND TIME(activelogs.start_time) >= RIGHT(CONCAT('0',numbers.n,':00'),5) AND TIME(activelogs.start_time) < RIGHT(CONCAT('0',numbers.n+1,':00'),5) WHERE numbers.n BETWEEN 0 AND 23 GROUP BY dates.thedate , time_slot

the reason i asked about what application language you’re using is because this query doesn’t do the “crosstab” or “pivot” layout, you can simply do that with arrays in php

1 Like

Hi @r937, Thank you for helping me.

can I ask if I have search form in my ( frontend ) . I would like to search from 2015-10-09 - 2015-10-25 , can I add between ? like this

SELECT dates.thedate,
       Concat(RIGHT(Concat('0', numbers.n, ':00'), 5), ' - ', RIGHT(
       Concat('0', numbers.n + 1, ':00'), 5)) AS time_slot,
       Count(activelogs.start_time)           AS thecount
FROM   numbers
       CROSS JOIN (SELECT DISTINCT Date(start_time) AS thedate
                   FROM   activelogs) AS dates
                  LEFT OUTER JOIN activelogs
                               ON Date(activelogs.start_time) = dates.thedate
                                  AND Time(activelogs.start_time) >= RIGHT(
                                      Concat('0', numbers.n, ':00'), 5)
                                  AND Time(activelogs.start_time) <
                                      RIGHT(Concat('0',
                                            numbers.n + 1, ':00'), 5)
WHERE  numbers.n BETWEEN 0 AND 23
       AND start_time BETWEEN 2015 - 10 - 09 AND 2015 - 10 - 25
GROUP  BY dates.thedate,
          time_slot 

Thank you in advance.

no, that’s not right

– those are arithmetic expressions you wrote there, not dates

– instead of this –

start_time BETWEEN '2015-10-09' AND '2015-10-25'

you actually want this –

start_time >= '2015-10-09' AND start_time < '2015-10-26'

– you need to add the above conditions into the subquery “dates” instead of the main query

Thank you :slight_smile:

@r937

Is this correct ?

SELECT dates.thedate,
           Concat(RIGHT(Concat('0', numbers.n, ':00'), 5), ' - ', RIGHT(
           Concat('0', numbers.n + 1, ':00'), 5)) AS time_slot,
           Count(activelogs.start_time)           AS thecount
    FROM   numbers
           CROSS JOIN (SELECT DISTINCT Date(start_time) AS thedate
                       FROM   activelogs
                              where start_time >= '2015-10-09' 
                       AND start_time  < '2015-10-26'
                       
                              ) AS dates
                      LEFT OUTER JOIN activelogs
                                   ON Date(activelogs.start_time) = dates.thedate
                                      AND Time(activelogs.start_time) >= RIGHT(
                                          Concat('0', numbers.n, ':00'), 5)
                                      AND Time(activelogs.start_time) <
                                          RIGHT(Concat('0',
                                                numbers.n + 1, ':00'), 5)
    WHERE  numbers.n BETWEEN 0 AND 23
          
    GROUP  BY dates.thedate,
              time_slot

also can I ask what those 5 stands for ?

Looking for this?

https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_right

RIGHT(str,len)

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

Thank you

what happened when you tested it? © ® ™

base on the output, I think it works, I will try to test again., I just ask if I put the correct condition in the subquery as what you suggested.

Thank you again.

Hi @r937,

Is it possible to count only once if the user 's start_time and end_time falls in a certain time

I have this data the user_id 5 has 3 records, and it falls to 17:00 - 18:00, I want only to count only once
for him in that time. is this possible ?

Thank you in advance.

+----+---------+---------------------+---------------------+
| id | user_id |     start_time      |      end_time       |
+----+---------+---------------------+---------------------+
|  1 |       5 | 2015-10-03 17:00:03 | 2015-10-03 17:15:03 |
|  2 |       5 | 2015-10-03 17:15:04 | 2015-10-03 17:30:01 |
|  3 |       5 | 2015-10-03 17:30:03 | 2015-10-03 17:45:05 |
|  4 |       8 | 2015-10-03 20:00:03 | 2015-10-03 20:15:03 |
|  5 |       8 | 2015-10-03 20:15:04 | 2015-10-03 20:30:01 |
|  6 |       8 | 2015-10-03 20:30:03 | 2015-10-03 20:45:05 |
+----+---------+---------------------+---------------------+

yes, it’s possible

look up the DISTINCT keyword in the manual

and when you try to apply it to your query, remember which returned column(s) you want made distinct