Mysql calculate sum of hours worked in last 7 days

Question

Want to calculate sum of total hours worked in last 7 days, I have total hours worked in a day. I have 4 events like study, school, playing and sleeping. I want to calculate total number of hours spent in study and school in last 7 days. I am working on Cakephp3.x Any query is welcome cakephp or mysql

$driversHos = $this->DriverLogs->DriverLogGraphs->find('all', [
        'conditions' => [
            'OR' => [
                ['DriverLogGraphs.event_type' => 0], // On duty
                ['DriverLogGraphs.event_type' => 1] // driving
            ],
            'DriverLogGraphs.driver_id' => $id,
            'DriverLogGraphs.start_time >=' => $thisMonthFirstDay
        ],
        'contain' => [ 
            'DriverLogs.Companies',
            'DriverLogs.Driver'
        ],
        'fields' => [
            'Driver.id', 
            'Driver.username', 
            'DriverLogs.driver_id',  
            'Companies.id',
            'Companies.name', 
            'DriverLogGraphs.id',  
            'DriverLogGraphs.driver_log_id', 
            'DriverLogGraphs.event_type',  
            'event_name' => "CASE  
                WHEN DriverLogGraphs.event_type = '0' THEN 'ON DUTY' 
                WHEN DriverLogGraphs.event_type = '1' THEN 'DRIVING'  
                WHEN DriverLogGraphs.event_type = '2' THEN 'SLEEPER BIRTH' 
                WHEN DriverLogGraphs.event_type = '3' THEN 'OFF DUTY'  
            END",
            'hos_date'  => "STR_TO_DATE(DriverLogGraphs.start_time,'%Y-%m-%d')",
            'hos_start' => "CONCAT(STR_TO_DATE(DriverLogGraphs.start_time,'%Y-%m-%d'),' ','00:00:00')",
            'hos_end'   => "CONCAT(STR_TO_DATE(DriverLogGraphs.start_time,'%Y-%m-%d'),' ','23:59:59')",

            'today_start' => "CONCAT(CURDATE(), ' ', '00:00:00')",
            'today_end' => "CONCAT(CURDATE(), ' ', '23:59:59')",

            'work_time'         => 'TIMESTAMPDIFF(SECOND, DriverLogGraphs.start_time, DriverLogGraphs.end_time)',
            'hours_work_today'  => 'SUM(TIMESTAMPDIFF(SECOND, DriverLogGraphs.start_time, DriverLogGraphs.end_time))',

            's_time_7_days' => 'CONCAT( STR_TO_DATE( DriverLogGraphs.start_time - INTERVAL 6 DAY,  "%Y-%m-%d" ) ,  "",  "" )',
            'e_time_7_days' => 'CONCAT( STR_TO_DATE( DriverLogGraphs.end_time,  "%Y-%m-%d" ) ,  "",  "" )',
            's_time_8_days' => 'CONCAT( STR_TO_DATE( DriverLogGraphs.start_time - INTERVAL 7 DAY,  "%Y-%m-%d" ) ,  "",  "" )',
            'e_time_8_days' => 'CONCAT( STR_TO_DATE( DriverLogGraphs.end_time,  "%Y-%m-%d" ) ,  "",  "" )',

            'hours_work_last_7_days' => '
                SUM(
                    IF (
                        DriverLogGraphs.start_time >= CONCAT(STR_TO_DATE(DriverLogGraphs.start_time - INTERVAL 6 DAY,  "%Y-%m-%d" ) ,  " ",  "00:00:00" ) 
                        AND 
                        DriverLogGraphs.end_time <= CONCAT(STR_TO_DATE(DriverLogGraphs.end_time,  "%Y-%m-%d" ) ,  " ",  "23:59:59" ), 
                        CASE  
                            WHEN DriverLogGraphs.event_type = "0" THEN TIMESTAMPDIFF(SECOND, DriverLogGraphs.start_time, DriverLogGraphs.end_time) 
                            WHEN DriverLogGraphs.event_type = "1" THEN TIMESTAMPDIFF(SECOND, DriverLogGraphs.start_time, DriverLogGraphs.end_time)  
                            WHEN DriverLogGraphs.event_type = "2" THEN "0" 
                            WHEN DriverLogGraphs.event_type = "3" THEN "0"  
                        END, 
                        ""
                    )
                )',
        ],      
        'group' => [
            'hos_date',
        ],
        'order' => [
            'hos_start' => 'DESC', 
        ]
    ])->hydrate(false)->toArray();

Check this image for quick understanding

check these links: https://stackoverflow.com/questions/15178319/mysql-query-issue-date-minus-7-days and https://stackoverflow.com/questions/42635297/sql-query-where-date-today-minus-7-days — Jigar Shah, Jul 02 '17 at 04:54
And if you're still struggling, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jul 02 '17 at 06:24

score 0 · Answer 1 · answered Jul 02 '17 at 05:28

0

You must have a field date in you table, student_id field to identify the student, Considering model=Events

$total_worked=$this->Events->find('all', [
 'conditions'=>[
          'date >='=>DATE(DATE_SUB(NOW(), INTERVAL 7 DAY)),
          'student_id' => $student_id
          ]
]);
$StudentWorkingHour='';
foreach($total_worked as $worked){
    $eachStudentWorkingHour=$StudentWorkingHour+($worked['study']+$worked['school'])
}

echo StudentWorkingHour; // Will result the total worked in one week

answered Jul 02 '17 at 05:28

bikash.bilz

821
1
13
33

check my hours_work_last_7_days in above my question, I am doing this – Pawan Kumar Jul 02 '17 at 06:00
I already have start_time and end_time and event_type (0,1,2,3) I just need to calculate sum of 0 and 1 event_type. Go through my image attached in my question – Pawan Kumar Jul 02 '17 at 06:02
sum of 0 and 1 event_type in last 7 days. If today is 30 then sum from 24 to 30 of the month. if today is 31 then sum from 31 to 25. I think i make you understand point now – Pawan Kumar Jul 02 '17 at 06:15

Mysql calculate sum of hours worked in last 7 days

1 Answers1