# Thread: How to adjest an calculated time?

1. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

#### Posting Permissions

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