✔ [MySQL] Überstunden berechnen unter Berücksichtigung der Pausen

[Johnnii360]

Servus zusammen!

Leider ist Mathematik nicht mein Gebiet in dem ich punkten kann. Ich habe bzgl. meines Problems auch schon viel gegoogelt, aber nichts nützliches gefunden.

Folgendes Problem: Ich möchte gerne die Überstunden unter Berücksichtung der Pausenzeiten berechnen. Dies für den aktuellen Monat.

Meine Tabelle ist so aufgebaut:

CREATE TABLE `timesheet` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `uid` INT(11) NULL DEFAULT NULL,
    `day` DATE NULL DEFAULT NULL,
    `daytype` VARCHAR(1) NULL DEFAULT NULL COLLATE 'utf8_german2_ci',
    `startofwork` TIME NULL DEFAULT NULL,
    `endofwork` TIME NULL DEFAULT NULL,
    `breaktime` TIME NULL DEFAULT NULL,
    `vacationday` TIME NULL DEFAULT NULL,
    `notes` TEXT NULL DEFAULT NULL COLLATE 'utf8_german2_ci',
    `added` DATE NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `id` (`id`),
    INDEX `uid` (`uid`)
)
COLLATE='utf8_german2_ci'
ENGINE=MyISAM;

Einen Schnipsel zur Berechnung der gearbeiteten Stunden habe ich bereits gefunden.
Hier in meiner angepassten Variante:

$worktimes_this_month = $mysqli->query("SELECT *,SEC_TO_TIME(SUM(TIME_TO_SEC(endofwork)-TIME_TO_SEC(startofwork)-TIME_TO_SEC(breaktime))) AS working_hours, FROM timesheet WHERE MONTH(day) AND day!=DAY(day)") or print('<span class="text-danger"><i class="material-icons">error</i> Fehler</span>');
$timerecord = $worktimes_this_month->fetch_assoc();

Auf Stackoverflow habe ich auch schon einen Schnipsel zur Berechnung der Überstunden gefunden. Allerdings habe ich keine Ahnung wie ich hier die Pausenzeiten mit einwebe.

Wäre schön, wenn mir hier jemand helfen könnte, denn ich stoß ehrlich gesagt hier an meine Verständnisgrenzen das Ganze zu berechnen.

 

[Yaslaw]

Da dein Code unlesbar ist, lege ich dir als Erstes dieses kleine Tutorial nahe: SQL-Statement in PHP lesbar darstellen
Ich lasse dein SQL mallinks leigen

Dein SQL in lesbarer Form

SELECT
    *,
    SEC_TO_TIME(
        SUM(
            TIME_TO_SEC(endofwork)-TIME_TO_SEC(startofwork)-TIME_TO_SEC(breaktime)
        )
    ) AS working_hours,
FROM timesheet
WHERE
    MONTH(day)
    AND day!=DAY(day)

1) Ein Komma zu viel. Vor dem FROM darf niemals ein Komma sein.
2) Was prüfst du da im Where? Das macht kein Sinn?
3) Du solltest keine Spalte mit DAY oder TIME bennen. Das sind Namen die bereits von SQL selber verwendet werden.
4) Zu einem SUM( gehört auch ein GROUP BY.
5) Bei einem SUM kannst du kein * verwenden.
Aus 4) und 5) ergibt sich die Frage, willst du das Resultat auf Datensatzbasis oder über irgendwas summiert? Ich würde die Summe erst bilden, wenn du sicher bist, dass die Restliche Berechnung funktioniert

Um solche Fehler selber zu finden, rate ich dir dieses Tutorial mal schenlle duchzulesen Debug Queries

Am besten wir beginnen bei 0.
a) Dann. Die Überstunden pro Tag? Odes was suchst du genau.
b) Und wie soll das berechnet werden?
(Endzeit-Anfangszeit) - Pausen
c) Wo finde ich die Soll-Zeit um die Überstunden zu berechnen?
d) Warum ist breaktime als Zeit drin? Ist ja keine Uhrzeit sondern ein Zeitspanne[/PHP][/URL]

 

[Johnnii360]

Zu 1. Sorry, hatte ich übersehen, hat aber trotzdem funktioniert.
Zu 2. Ich will, dass er nur die Datensätze des aktuellen Monats aber nicht des heutigen Tages nimmt.
Zu 3. MariaDB meckert aber nicht rum. Scheint daher kein Problem zu sein. Kann's aber auch gerne vorsorglich abändern.
Zu 4. Okay, wusst ich nicht. Ich bin aber auch schon eine Weile aus der Materie draußen, von daher kann ich mal was vergessen haben.
Zu 5. Hab doch nirgends ein * verwendet?

Zu a) Die Überstunden sollen pro Monat sein. Das ganze wird dann auf dem Dashboard angezeigt.
Zu b) Genau, so in die Richtung. Es ist ja so, dass man auch über die Kernarbeitszeit von 8 Stunden drüber hinausarbeiten kann. Allerdings ist ja dann auch noch die Möglichkeit die Pausenzeit zu verkürzen, weswegen sich dann die Überstunden nochmals verlängern. Hier hab ich eben das Problem, dass ich nicht weiß, wie ich das Ganze berechnen soll. Wie gesagt, Mathe ist nicht mein Steckenpferd, eher Rechtschreibung und Grammatik. ;D
Zu c) Stimmt... Die ist in der Tabelle "profiles" - zumindest für jeden Monat. Diese ist jedoch variabel. In meinem Fall wären das 176,30 Stunden im Monat, täglich dann 8. Aber nicht jeder Monat kann mit vollen 176,30 Stunden gefüllt werden, da nicht jeder Monat die gleiche Tagesanzahl hat. Ach ja, das Feld hier heißt dann "fullworkingtime" für einen Monat.
Zu d) Ich habe breaktime als Zeit festgelegt, damit ich ein bisschen besser "hantieren" kann. Es geht hier letztendlich um eine Zeiteinheit in Industrieminuten.

Ich mache nun Feierabend und schaue dann morgen wieder rein.
Bis dann!

 

[Yaslaw]

Zu 5): So stehts in deinem Code: SELECT *,SEC...


Mit den 8 Stunden komme ich mit MySQL (MariaDB müsste fast gleich sein) auf das folgende SQL

select
  month(d.day),
  sum(d.worktime_sec) as monthly_workingtime,
  sum(d.daily_fullworkingtime_sec) as monthly_fullworkingtime_sec,
  sign(sum(d.daily_diff_sec)) as monthly_diff_sign,
  sec_to_time(abs(sum(d.daily_diff_sec))) as monthly_diff
from
  (
    select
      t.day,
      time_to_sec(timediff(timediff(t.endofwork, t.startofwork), t.breaktime)) as worktime_sec,
      time_to_sec('08:00:00') as daily_fullworkingtime_sec,
      time_to_sec(timediff(timediff(t.endofwork, t.startofwork), t.breaktime)) - time_to_sec('08:00:00') as daily_diff_sec
    from timesheet t
  ) d
 group by
   month(d.day)

Hier noch die Spielwiese: http://sqlfiddle.com/#!9/b14105/14

 

[Johnnii360]

Cool danke, scheint zu funktionieren!

Hier die Ausgabe:

Array
(
    [MONTH(d.DAY)] => 1
    [monthly_workingtime] => 365520
    [monthly_fullworkingtime_sec] => 374400
    [monthly_diff_sign] => -1
    [monthly_diff] => 02:28:00
)

Ich denke monthly_diff sind dann die Überstunden, richtig?

 

[Yaslaw]

Jepp. Das sind die überstunden. Und monthly_diff_sign ist das Vorzeichen. In dem Fall -2:28 Stunden
Es gibt im Zeitformat kein Minus....

 

[Johnnii360]

Super, vielen Dank!

 

[Johnnii360]

Hmm, also so richtig berechnet wird das aber auch nicht. Heute arbeite ich weniger, wird aber nicht von meinen Überstunden abgerechnet. Und dafür, dass ich jeden Tag nur 30 Minuten Pause mache (00:50), müsste ich eigentlich auch mehr haben.

Ich hab Dir mal den timesheet angehängt.

Ich hab den Code auch nochn bissl angepasst:

SELECT
    d.uid,
    MONTH(d.day),
    SEC_TO_TIME(SUM(d.worktime_sec)) AS monthly_workingtime,
    SUM(d.daily_fullworkingtime_sec) AS monthly_fullworkingtime_sec,
    SIGN(SUM(d.daily_diff_sec)) AS monthly_diff_sign,
    SEC_TO_TIME(ABS(SUM(d.daily_diff_sec))) AS monthly_diff
FROM
(
    SELECT
        t.uid,
        t.day,
        TIME_TO_SEC(TIMEDIFF(TIMEDIFF(t.endofwork, t.startofwork), t.breaktime)) AS worktime_sec,
        TIME_TO_SEC('08:00:00') AS daily_fullworkingtime_sec,
        TIME_TO_SEC(TIMEDIFF(TIMEDIFF(t.endofwork, t.startofwork), t.breaktime)) - TIME_TO_SEC('08:00:00') AS daily_diff_sec
    FROM timesheet t
) d
WHERE MONTH(d.day) AND d.day!='".date("Y-m-d")."' AND d.uid=".$_SESSION['uid']."
GROUP BY MONTH(d.DAY) AND d.uid

Das Ergebnis sieht dann so aus:
Gearbeitet, diesen Monat (Ist/Soll) 109:49/176.30
Überstunden diesen Monat 02:11

Das kann nicht ganz stimmen mit den Überstunden.

 

[Yaslaw]

Ist schwer zu sagen, wenn man die Testdaten nicht hat. Mit meinen Testdaten rechnet es richtig: http://sqlfiddle.com/#!9/b14105/14

 

[Johnnii360]

Hab doch oben die Daten angehängt. (timesheet_db.sql) Sind alle Daten drin. Oder brauchst Du noch die Profildaten?