Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008

    Unanswered: How to adjest an calculated time?

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

    	    		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
    In front of the computer
    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:
    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.

    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