INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

sum total hours between datetime stamp

sum total hours between datetime stamp

(OP)
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

RE: sum total hours between datetime stamp

(OP)
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)

RE: sum total hours between datetime stamp

CODE

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.

RE: sum total hours between datetime stamp

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

RE: sum total hours between datetime stamp

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.



RE: sum total hours between datetime stamp

following on from the last late-night post:

I populated a db table using php thus

CODE

<?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

CODE

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.


RE: sum total hours between datetime stamp

results looked like this

CODE

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) *"

CODE

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 

RE: sum total hours between datetime stamp

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)

CODE

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.

CODE

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 



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close