Count number of hours

Databases
1

@r937

I have this table structure,

How can I query to get the number of hours base on the date from and to
example if my datefrom will be 2015-09-07 and the dateto will be 2015-10-11
and the time between this 6:00 am - 10 pm
I want to count the total number of hours that the user being active for that range datefrom and dateto and timefrom and timeto I set.

CREATE TABLE `activelogs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `status` VARCHAR(10) NOT NULL,
    `dateactive` DATETIME NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Here is my data.

id    user_id        status        dateactive

1        24            active      2015-10-01 06:10:01    

2        24            active      2015-10-01 06:10:06    

3        24            active      2015-10-01 06:10:11    

4        24            active      2015-10-01 06:10:16    

5        24            active      2015-10-01 06:10:21    

6        24            active      2015-10-01 06:11:00
    
7        24            active      2015-10-01 06:11:10    

8        24            active      2015-10-01 06:11:20    

9        24            active      2015-10-01 07:10:00    

10        24            active      2015-10-01 07:10:15
    
11        24            active      2015-10-01 07:20:00    

12        24            active      2015-10-01 07:10:00

13        24            active      2015-10-01 07:10:00

14        24            active      2015-10-01 07:10:00

15        24            active      2015-10-01 07:10:00

.....                
.....    
ectc....          

        24          inactive    2015-10-01 10:10:00
        
        24          inactive    2015-10-01 10:10:25
        
        24          inactive    2015-10-01 10:11:00
        
        24          inactive    2015-10-01 11:10:00
        
        24          inactive    2015-10-01 11:10:20
        
....
....
etc...

        24          active      2015-14-01     05:05:45

Thank you in advance.

2

Personally I would have calculated the hours as I went along. Take the active time from the inactive time and written that into the database. You could also have a running total calculated at the same time.
This means you could do away with the deactive date/time data as you could calculate it if you needed it later.

Also why do you have the date/time in a column called deaactive as it is used for both active and inactive results?

If you are going to keep this much data and you have a lot of users you table is going to get very large very quick. A database design change may be required?

3

Thank you for the reply, dateactive is the date inerted to my table, or maybe I would rename that to dateinserted to prevent confusing…

yes my table will get large quickly because I am want to have the details of all users there date for being active and inactive.

4

Wouldn’t it be better to have an “active log” table for each user?

The database would still get large, I know databases can handle “largeness” well in most cases, but the idea of such a large table with every date for every user is a scary thought.

5

[quote=“jemz, post:1, topic:205017, full:true”]
@r937 [/quote]oh hai

[quote]if my datefrom will be 2015-09-07 and the dateto will be 2015-10-11
and the time between this 6:00 am - 10 pm[/quote]

two questions:

  1. can you confirm that “active 2015-09-07 22:09:37” should ~not~ be counted

  2. how do you want to count hours? integer hours only, or exact conversion of total minutes?

also, i should warn you that the solution i give you, assuming i am able to produce one, will be inefficient

it will involve correlated subqueries, never a happy scenario

6

@r937

should not be counted .

Integers hours only.

Thank you in advance.

7

yeah but I have no choice, I have to track there active and inactive status, because I want to produce report for this.

8

Are you getting soft in your old age @r937 or has the quality of questions worn you down over the years :smiley:

1 Like
9

I guess not, evem MySQL itself says

https://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html

they are inefficient and likely to be slow

10

Hi @r937, @DaveMaxwell

I change my table structure to this

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
;

here is the sample data

id    user_id              start_time                           end_time

1        24                  2015-09-07 07:10:01                   2015-09-07 10:20:20

2        24                  2015-09-07 12:10:00                   2015-09-07 15:00:00     

3        24                  2015-09-07 16:00:00                   2015-09-07 22:20:20

I want to get the number of hours of user being active on this search date

from 2015-09-07 06:00:00 to 2015-09-11 22:00:00

Thank you in advance.

11

If I understand what you’re asking, you’d need to do something like this:

SELECT user_id
     , DATE_DIFF(MAX(end_time), MIN(start_time)) AS ActivityTime
  FROM activelogs
 GROUP BY user_id

You’ll probably want to format the activity time but since I don’t know what interval you’re looking, I’ll let you dig through that (date time functions are here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html)

12

I got error function DATE_DIFF doesn’t exist

13

Typo, try removing the underscore

14

Thank you

15

@DaveMaxwell,

I need to get total hours being active base on this clause

BETWEEN ‘2015-09-07 06:00:00’ AND ‘2015-09-11 22:00:00’

16

OK…so add the correct comparison(s) in your where clause.

  • Are you looking for things that START between those date ranges
  • Are you looking for things that END between those date ranges
  • Are you looking for things that START AND END between those two date ranges

You can use the MAX and MIN dates in your where clause as well as your select portion. You just need to figure out which ones are appropriate to your situation.

17 
SELECT id
     , DATEDIFF(MAX(end_time), MIN(start_time)) AS ActivityTime
  FROM activelogs
  WHERE start_time BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 22:00:00'
  AND end_time BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 22:00:00'
 GROUP BY user_id

How can I use the max and min where clause ?

18

but, but, but…

according to post #6, you also want to exclude the hours from 10pm to 6am, right?

19

Oops. MAX() and MIN() need to be moved into HAVING

SELECT field
     , MIN(field2)
     , MAX(field3)
  FROM tableName
 GROUP BY field
HAVING MIN(field1) BETWEEN 1 AND 10
   AND MAX(field2) BETWEEN 2 AND 20
20

yes, I want to get the total hours of user of how many hours he is being active with this
from ‘2015-09-07 06:00:00’ to ‘2015-09-11 22:00:00’

I have this solution but still it continues counting beyond the 22:00:00 or 10pm time

SELECT SUM(HOUR(TIMEDIFF(end_time, start_time))) AS 'hour'
FROM   activelogs
WHERE  start_time BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 22:00:00'
and end_time BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 22:00:00'
and user_id = 24

for example in day 2015-09-07, I want to count the hours being active
for this date range ‘2015-09-07 06:00:00’ AND ‘2015-09-11 22:00:00’,
then in day 2015-09-08 count the hours for this date range
‘2015-09-07 06:00:00’ AND ‘2015-09-11 22:00:00’, up to the day 2015-09-11
with the date range ‘2015-09-07 06:00:00’ AND ‘2015-09-11 22:00:00’.

with the time for each day 06:00:00 to 22:00:00.

Thank you in advance.