Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2011
    Posts
    84

    Unanswered: counting hours inside from datetime range and outside datetime range

    can I ask some help.

    I have this schema

    Code:
    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
    ;
    I want to get the the total hours for inside datetime range and outside datetime range

    datetime range '2015-09-07 06:00:00' to '2015-09-11 22:00:00'

    I want to get the hours of user=24 that is being active for that datetime range specified
    and also I want to get the total hours outside in that datetime range.

    please see my data attachment.


    HTML Code:
    +---------------+---------+----------------------+----------------------+
    | id    user_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 |
    |               |         |                      |                      |
    |             4 |      24 |  2015-09-07 23:25:20 |  2015-09-08 05:00:00 |
    +---------------+---------+----------------------+----------------------+
    
    Attached Thumbnails Attached Thumbnails sam.PNG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •