Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58

    Unanswered: How to adjest an calculated time?

    I have this query, it is about registration working hours of our personnel.


    Code:
    SELECT
    	    		TIME_FORMAT(start,'%H:%i') AS start_tijd,
    	    		TIME_FORMAT(eind,'%H:%i') AS eind_tijd,
    	    		TIME_FORMAT(TIMEDIFF(eind, start),'%H:%i') AS gewerkt,
    	    		TIME_FORMAT(start_correctie,'%H:%i') AS start_correctie,
    	    		TIME_FORMAT(eind_correctie,'%H:%i') AS eind_correctie,
    	    		TIME_FORMAT(TIMEDIFF(eind_correctie, start_correctie),'%H:%i') AS gewerkt_correctie,
    	    		TIME_FORMAT(pauze,'%H:%i') AS pauze,
    	    		TIME_FORMAT(pauze_correctie,'%H:%i') AS pauze_correctie
    	    		FROM users_uren
    I have calculated gewerkt (total working time), how can I remove the break they have taken (pauze) from the total working time?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The "relationally correct" way to accomplish this is to consider either each begin/end pair or each event as a separate thing. The schema/structure that you've created makes the computation much more difficult than it needs to be.

    As an example, if you consider each pair to be a "unit", then you get something like:
    Code:
    CREATE TABLE Meine_Zeit_Paar (
       innie    DATETIME  NOT NULL
    ,  outie    DATETIME  NOT NULL
       );
    
    SELECT TIME_FORMAT(Sum(TIMEDIFF(eind, start)),'%H:%i') AS gewerkt
       FROM Meine_Zeit_Paar;
    If you get more precise and consider each event discreetly, then you need to write a bit more complex SQL but the concept remains the same.

    By using a simpler model, you get simpler and more robust SQL that copes with unanticipated issues better.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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