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