sum total hours between datetime stamp 1

[Bilberry]

Hi All,
I have a table with the following content:
Datetime | StartStop |
21-03-2014 17:40 | Stop
21-03-2014 12:40 | Start


I want to sum the hours a day. How can i realisze that. I want to have:

Date - Hours worked
21-02-2014 - 5

 

[Bilberry]

It is possible to work in the morning for 3 hours and in the evening for 4 hours (Same day). How can i the hours worked (with start/stop)

 

[jpadie]

Select 
 cast(`Datetime` as date),
 timediff(Max(`datetime`), min(`datetime`))
From timesheet
Group by cast(`datetime` as date) asc

That will return a value in hrs:mins:secs

If you really want just the hour portion (so that 20:34:32 becomes 20) then wrap the timediff in the hour function.

This only works for one start and one stop per day. If that is not the case then you should be asking a different question.

 

[ChrisHirst]

Not going to happen in a single query, you will need a stored procedure to collate the dates, especially if the records are in individual rows or not sequential.
Unless you are going to run one query for every date.

Basically you are dealing with a poorly designed data structure for running such queries against.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[jpadie]

I think you can do it one query provided the business rules prescribe that there must always be a stop time for every start time for every calendar day.

If so is it not the sum of all stop timestamps (expressed as Unix time) less the sum of.all start timestamps on that day. Then just reconvert to hour format? Or divide by 3600 to get a decimal.

Off the top of my insomniac head this should be possible as a single grouping query (with the result being derived from two summed case statements) or a join on thebsame table referenced thrice or a query with columns derived from subselects.

Choice will depend on optimisation and whether my brain is not addled. I'd go for the case approach I think. Single pass solution with no temp tables so should be fastest

Of course things break down if there is a stop event with no start or if someone works past midnight and the software does not automatically clock them out at 2359 and back in again at 00:00. For that reason I'd probably approach the whole problem programmatically in the application language.

 

[jpadie]

following on from the last late-night post:

I populated a db table using php thus

<?php
$pdo = new PDO('mysql:host=127.0.0.1;port=8889;dbname=datetest', 'root','******');
$pdo->exec('drop table dbtest');
$pdo->exec("create table dbtest (ts timestamp, t enum('start','stop'))");
$pdo->exec('SET @@session.time_zone = "+00:00"');
date_default_timezone_set('UTC');
$now = strtotime('2014-03-23 09:00');
$i = $pdo->prepare('insert into dbtest(ts, t) values (from_unixtime(?), ?)');
$timePeriods = array(0.5, 1, 1.5, 2, 2.5, 3);
$diffPeriods = array(15, 30, 45, 60);
for($ii=1; $ii<=200;$ii++):
    for($j = 0; $j<3; $j++):
        $tpRand = rand(0, count($timePeriods) - 1);
        $dpRand = rand(0, count($diffPeriods) - 1);
        if($j == 0):
            $curTime = strtotime("-$ii day", $now);
        else:
            $curTime = strtotime("+" . $diffPeriods[$dpRand] . " minute", $curTime);
        endif;
        $result = $i->execute(array($curTime, 'start')); //enter the start time
        $curTime = strtotime("+" . (60 * $timePeriods[$tpRand]) . " minute", $curTime);
        $i->execute(    array(  $curTime,'stop' ) );
    endfor;
endfor;

so assumptions were:
1. using arbitrary 'clock-in' periods from between 30 mins and 3 hours and an arbitrary duration between clock out and clock in of between 15 and 60 mins.
2. three clock-in/out actions per day
3. always one clock-out for each clock-in.

this created a neat sample of 3 in/outs per day for the last 200 days.

then I used this query to retrieve the time spend per day

SELECT
        CAST(ts AS DATE) as `day`,
        SEC_TO_TIME(
                    SUM(
                        TIME_TO_SEC(
                                    TIME(
                                            CASE t 
                                            WHEN 'stop' 
                                            THEN ts 
                                            ELSE 0 
                                            END
                                        )
                                    )
                    ) -
                    SUM(
                        TIME_TO_SEC(
                                    TIME(
                                            CASE t 
                                            WHEN 'start' 
                                            THEN ts 
                                            ELSE 0 
                                            END
                                        )
                                    )
                    )
    ) AS timespend
FROM dbtest
GROUP BY CAST(ts AS DATE) DESC

using this method, mysql reported the query (admittedly over only 200 sets of six rows) as taking 0.0057 seconds. which seems ok.

 

[jpadie]

results looked like this

day         timespend
2014-03-22  05:30:00
2014-03-21  07:00:00
2014-03-20  02:30:00
2014-03-19  06:00:00
2014-03-18  07:00:00
2014-03-17  03:30:00
2014-03-16  02:30:00

to get this in decimals replace the words "sec_to_time" with "(1/3600) *"

day         timespend
2014-03-22  5.5000
2014-03-21  7.0000
2014-03-20  2.5000
2014-03-19  6.0000
2014-03-18  7.0000
2014-03-17  3.5000

 

[jpadie]

here is a link to a sql dump (in zip) if anyone wants to load some dummy data. I have extended it to 1000 days to get a better idea of query execution time on larger datasets. I have also added a userID parameter to make the exercise more like a real world example and dummied 100 users into the equation.

the associated query would look like this (to retrieve the timesheets for the current month for a given set of users)

SELECT
  
  CAST(ts AS DATE) as `day`,
  userID,
  SEC_TO_TIME(
     SUM(
      TIME_TO_SEC(
         TIME(
           CASE t 
           WHEN 'stop' 
           THEN ts 
           ELSE 0 
           END
          )
         )
     ) -
     SUM(
      TIME_TO_SEC(
         TIME(
           CASE t 
           WHEN 'start' 
           THEN ts 
           ELSE 0 
           END
          )
         )
     )
  ) AS timespend
FROM dbtest
WHERE YEAR(NOW()) = YEAR(ts) AND MONTH(NOW()) = MONTH(ts)
GROUP BY CAST(ts AS DATE) DESC, userID ASC
HAVING userID IN (1,10,15)

The query took quite a bit longer this time, clocking in (!) at 1.1447 seconds. So I ran a profile against it and found that it was dumping to a temp table (which took 1.3 out of the 1.6 secs that the profiling operation took). It may be worth looking at different structures to optimise this although the performance doesn't seem terrible.

day                 userID    timespend
2014-03-22          1         05:30:00
2014-03-22          10        04:00:00
2014-03-22          15        05:30:00
2014-03-21          1         04:30:00
2014-03-21          10        04:30:00
2014-03-21          15        05:30:00
2014-03-20          1         05:30:00
2014-03-20          10        02:30:00
2014-03-20          15        05:00:00
2014-03-19          1         05:30:00
2014-03-19          10        07:00:00
2014-03-19          15        04:30:00
2014-03-18          1         08:00:00
2014-03-18          10        04:00:00
2014-03-18          15        01:30:00
2014-03-17          1         07:00:00
2014-03-17          10        03:30:00
2014-03-17          15        06:30:00
2014-03-16          1         02:00:00
2014-03-16          10        06:30:00
2014-03-16          15        07:30:00
2014-03-15          1         06:30:00
2014-03-15          10        04:30:00
2014-03-15          15        05:30:00
2014-03-14          1         06:30:00
2014-03-14          10        06:00:00
2014-03-14          15        04:30:00
2014-03-13          1         05:00:00
2014-03-13          10        03:30:00
2014-03-13          15        06:30:00
2014-03-12          1         07:00:00
2014-03-12          10        05:30:00
2014-03-12          15        03:00:00
2014-03-11          1         04:30:00
2014-03-11          10        06:00:00
2014-03-11          15        03:30:00
2014-03-10          1         08:00:00
2014-03-10          10        07:00:00
2014-03-10          15        05:00:00
2014-03-09          1         02:00:00
2014-03-09          10        03:30:00
2014-03-09          15        03:30:00
2014-03-08          1         01:30:00
2014-03-08          10        06:00:00
2014-03-08          15        04:30:00
2014-03-07          1         03:30:00
2014-03-07          10        05:30:00
2014-03-07          15        06:00:00
2014-03-06          1         06:30:00
2014-03-06          10        05:30:00
2014-03-06          15        04:30:00
2014-03-05          1         06:30:00
2014-03-05          10        03:30:00
2014-03-05          15        04:30:00
2014-03-04          1         05:30:00
2014-03-04          10        08:30:00
2014-03-04          15        06:00:00
2014-03-03          1         04:30:00
2014-03-03          10        03:30:00
2014-03-03          15        04:30:00
2014-03-02          1         02:30:00
2014-03-02          10        05:30:00
2014-03-02          15        06:00:00
2014-03-01          1         08:30:00
2014-03-01          10        03:30:00
2014-03-01          15        06:30:00