# mit Zeiten rechnen - Ermittlung der Arbeitszeit aus MySQL-Tabelle



## dwex (14. April 2005)

hallo leute,

ich habe folgendes problem.
ich würde gerne bei mir im büro eine "einfache" zeiterfassung realisieren.
wir haben einen webserver mit php und mysql laufen.

ich bekomme die zeiten (als datum und eine zweite/dritte spalte für die zeiten (start und stop) wie im mysql üblichen format oder auch als (unix)-timestamp in die mysql-tabelle) das ist kein problem
jetzt kommts aber - ich möchte natürlich am monatsende die zeiten auswerten können.
dazu müsste ich folgendes tun:

1. alle zeiten eines monats mit datum für jeden benutzer einzeln auslesen (das bekomme ich hin und in ein arry gespeichert)
2. jetzt müsste ich aber die die arbeitszeit (also die differenz aus start- und stopzeit) berechnen - und das bekomme ich nicht mehr hin.
3. sollten ja die differenzen dann auch zusammengezählt werden - da habe ich auch keine ahnung wie ich das machen soll (if oder for oder while schleife zusätzlcih inder schleife und die ganze geschichte in ein array schreiben - keine ahnung).

wer kann helfen?

vielen dank für eure hilfe


----------



## redlama (15. April 2005)

Hallo!

Ich würde es so machen:
Du speicherst für jeden Mitarbeiter den Tag im Format date (Bsp: 2005-04-15) und die Start- und Stopzeit als unix_timestamp (Bsp: 1113544800, 1113577200).
Dann liest Du die Daten des gesuchten Zeitraumes aus und hast sie in einem Array.
In einer 2. Schleife liest Du dann in dem Array die Werte für jeden Tag aus:
	
	
	



```
$start = "1113544800";
$stop = "1113577200";
$diff = $stop - $start;
$diff = $diff / 3600;
echo $diff;
```
$diff hat dann den Wert der gearbeiteten Stunden. Du musst dann evtl. noch die Pausenzeiten herausrechnen.
Und $diff speicherst Du dann in einer $zeit (oder wie auch immer) und addierst den Wert eines jeden Tages hinzu.
Damit hast Du dann am Ende des Monats die gearbeiteten stunden.

redlama

P.S. Übrigens willkommen an Bo(a)rd! Da Du neu bist, möchte ich Dich auf die hier gültige Netiquette hinweisen. Dort wird unter anderem auch etwas über die Groß-/Kleinschreibung auf tutorials.de gesagt.


----------



## hpvw (15. April 2005)

Um mal einen weiteren Vorschlag in den Raum zu werfen:
Ich würde das Datum nicht extra speichern, sondern Start und Stop als MySQL-DATETIME ablegen und mit den SQL-Datums- und Zeitfunktionen arbeiten.
Das ganze kannst Du direkt im SQL-Statement mit GROUP BY beliebig aggregieren oder auch tageweise ausgeben.

Eintragen ist auch ganz einfach: Wenn der Mitarbeiter kommt, klickt er einen Button "Ich bin da". Du machst einen INSERT und belegst Start mit NOW().
Wenn er geht klickt er auf "Bin weg". Daraufhin machst Du ein UPDATE und trägst bei Stop NOW() ein.

Gruß hpvw


----------



## dwex (27. April 2005)

Hallo,

das mit der SQL-Methode gefällt mir eigentlich ganz gut.
ich habe folgendes gemacht.

Ich habe eine MySQL-Tabelle mit den Spalten id - user - kommen - gehen - status
id - fülle ich mit auto_increment auf:

So ich mache nun einen Eintrag in der Datenbank - dann steht da folgendes

id - user - kommen - gehen - status
1 - name - 2005-04-27 17:00:00 - 0000-00-00 00:00:00 - im Haus

Wenn ich nun den Button "Abmelden" klicke dann mache ich ein Update über die ID
Das Ganze sieht dann so aus:

id - user - kommen - gehen - status
1 - name - 2005-04-27 17:04:45 - 2005-04-27 18:35:07 - Ausserhaus

Das klappt ja bestens soweit bin ich - jetzt kommts aber mit den Abfragen wann ein Mitarbeiter da war.

Ich hätte mir in der MySQL-Befehlsrefernz die Geschichte mit MONTH(datum) ausgesucht.
Nur kapiere ich offensichtlich die Syntax nicht so ganz - wie muss mein SQL-Query aussehen damit ich z.B. alle Einträge eines bestimmten Nutzers wiederum eines bestimmten Monats als Ausgabe bekomme.

Habe schon ein bischen experimentiert aber bin auf nichts gekommen.
Habe z.B. folgende Query gebaut:


```
$abfrage = mysql_query("SELECT * FROM zeitkarte WHERE MONTH(NOW());") OR die(mysql_error());
```

Mit der bekomme ich alle Einträge raus - auch welche nicht dem aktuellen Monat entsprechen.
Wenn ich statt "NOW()" ein Datum angebe dann habe ich das gleiche Problem.

Es wäre sehr nett wenn Ihr mir kurz das erklären könntet und mich vielleicht mit einen Codeschnippsel bezüglich der Abfrage unterstützen könntet.

Vielen Dank im voraus.


----------



## hpvw (27. April 2005)

```
SELECT * FROM zeitkarte WHERE MONTH(kommen)=MONTH(NOW())
```
Du solltest auch mit dem aktuellen Monat irgendwas vergleichen


----------



## dwex (27. April 2005)

Hallo,

vielen Dank erstmal - was würde ich denn ohne euch machen - ich wäre aufgeschmissen.

Eine Frage hätte ich dann noch.

Wie kann ich denn jetzt mit diesen beiden Zeiten rechnen.
Ich möchte ja die Stunden und Minuten eines jeden Eintrags ermitteln und am Monatsende das auch noch als Gesamtsumme ausgeben.

Geht das vielleicht auch mit einer SQL-Abfrage oder muss ich da mit PHP rechnen und wie geht dann das bzw. wo kann ich mir das Nachlesen.

Vielen Dank nochmals


----------



## hpvw (27. April 2005)

Der Link zu den Datums- und Zeitfunktionen steht oben zum Nachlesen.
Im Select müsste sowas in der Art funktionieren:
	
	
	



```
SEC_TO_TIME(UNIX_TIMESTAMP(gehen) - UNIX_TIMESTAMP(kommen))  AS zeit
```


----------



## dwex (27. April 2005)

Hallo nochmal,

anscheinend bin ich zu blöd (heute) dafür.

zu der Abfrage habe ich noch ein paar Fragen:

1. Wenn ich nun die Abfrage so hinbekomme und die Abfrage dann in ein Array schreibe (mit mysql_fetch_array) dann habe ich ein Arrayelement mit dem Namen "zeit" sehe ich das richtig?

2. ich habe die Abfrage mal in phpmyadmin probiert und zwar folgende:

```
SELECT * 
FROM `zeitkarte` SEC_TO_TIME( UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) ) AS zeit
LIMIT 0 , 30
```
auch habe ich probiert:

```
SELECT * 
FROM `zeitkarte` 
WHERE SEC_TO_TIME( UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) ) AS zeit
LIMIT 0 , 30
```
und ebenfalls - weil so sollte es ja sein (zumindest glaube ich das):

```
SELECT * 
FROM `zeitkarte` 
WHERE id = 1
AND SEC_TO_TIME( UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) ) AS zeit
LIMIT 0 , 30
```

Da schmeisst mir phpmyadmin nur einen Syntaxfehler aus:

```
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS zeit LIMIT 0 , 30' at line 1
```
Was mache ich hier falsch?
Bitte lacht mich jetzt nicht aus - aber ich hab das ganze immer noch nicht kapiert und bin ja auch noch nicht lange dabei mit PHP und schon garnicht mit MySQL

Vielen Dank im voraus (schäm)


----------



## hpvw (27. April 2005)

Das ist ein "Feld" was Du auswählst:

```
SELECT 
id,
user,
kommen,
gehen,
status,
SEC_TO_TIME( UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) ) AS zeit
FROM `zeitkarte` 
LIMIT 0 , 30
```


----------



## dwex (27. April 2005)

Hallo,

wann werde ich das begreifen.

DANKE das hat wunderbar geklappt - aber warum funktioniert das nicht mit:

SELECT *

Warum muss ich hier alle Felder der DB angeben.

(Wiedereinmal) Vielen Dank im voraus

PS: Vielen Dank für deine Geduld


----------



## hpvw (28. April 2005)

Du könntest mal versuchen, mit  SELECT *, SEC_TO_TIME ... AS zeit ... das gleiche zu erreichen. Ich weiss aber nicht, ob das zulässig ist. 

Mit dem Ausdruck führst Du der Ergebnistabelle ein Feld mit Aliasnamen (AS) zu denen aus der Tabelle hinzu. Dieses neue Feld wirst Du mit * nicht erreichen.

Ich habe mir mittlerweile angewöhnt die Felder immer explizit auszuwählen und kann das nur empfehlen. Du hast direkt in Deinem PHP-Code die Übersicht, welche Felder Dir zur Verfügung stehen, ohne nebenbei in die Datenbank schauen zu müssen. Der wesentliche Vorteil ist jedoch, dass Du die Performance des Querys steigerst, wenn Du nicht alle Felder brauchst und nur die benötigten auflistest.

Gruß hpvw


----------



## dwex (28. April 2005)

Hallo,

ja stimmt - es macht ja auch durchaus Sinn wenn man nur die Felder auswählt die man braucht. Ich werde mir den Tipp zu Herzen nehmen und das künftig so machen.

So und schon wieder hat sich dich nächste Frage aufgeworfen.

Kann ich mehrere Datumsfunktionen nicht in eine Abfrage schreiben?
Wenn ich z.b. folgenden Code in die Abfrage schreibe funktioniert es:

```
SELECT 
id,
user,
kommen,
gehen,
status,
SEC_TO_TIME( UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) ) AS zeit
FROM `zeitkarte` 
WHERE user='dani'
LIMIT 0 , 30
```

wenn ich dann folgende Abfrage schreibe funktionier es auch:

```
SELECT 
id,
user,
kommen,
gehen,
status,
DATE_FORMAT(kommen, '%d.%m.%Y') AS tage
FROM `zeitkarte` 
WHERE user='dani'
LIMIT 0 , 30
```

Wenn ich jedoch folgendes schreibe:

```
SELECT 
id,
user,
kommen,
gehen,
status,
SEC_TO_TIME( UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) ) AS zeit
AND DATE_FORMAT(kommen, '%d.%m.%Y') AS tage
FROM `zeitkarte` 
WHERE user='dani'
LIMIT 0 , 30
```
dann funktioniert es nicht mehr.

Ich habe auch schon versucht das AND aus der Abfrage raus zu nehmen - aber ich bekomme immer einen Syntax-Fehler.
Und mit den Fehlerbeschreibungen von phpmyadmin kann ich nicht sehr viel anfangen.
Gibt es da eine Referenz im Netzt wo solche Leute wie ich auch selbst auf den Fehler kommen können.

Kann man die Anfragen nicht kombinieren?

Vielen Dank im voraus für die Hilfe


----------



## hpvw (28. April 2005)

hpvw hat gesagt.:
			
		

> Mit dem Ausdruck führst Du der Ergebnistabelle ein Feld mit Aliasnamen (AS) zu denen aus der Tabelle hinzu.


Mit dem Hinweis sollte es möglich sein, den Fehler selbst zu finden.
Du willst der Abfrage nun noch ein Feld hinzufügen. Dieses trennt man von den anderen Feldern mit einem Komma ab, wie man es mit allen Feldern tut, die man einzeln aufführt.
Mit dem logischen Operator AND hat das nichts zu tun.

Ansonsten hilft auch die Sprachreferenz.

Gruß hpvw


----------



## dwex (28. April 2005)

Hallo mein Held,

VIELEN VIELEN DANK!

Es funktioniert - und jetzt habe ich das dumpfe Gefühl das ich langsam durchsteige.

Ein Problem habe ich noch - aber das kann ich villeicht selber lösen - ausserdem kommt das erst in der weiteren Programmierung auf mich zu - da mache ich mir jetzt noch keine Sorgen drüber.

Danke nochmals


----------



## dwex (3. Mai 2005)

Hallo,

so nun bin ich wieder mal an meine Grenzen gestossen.
Ich habe es soweit geschaft das die Zeiten in der Tabelle eingetragen werden und die Zeitdiffernz ausgerechnet und auch eingetragen wird.
Ausserdem habe ich eine neue Tabellenspalte eingefügt diese heisst "zeitstempel" in dieser Spalte speichere ich die jeweils gearbeiteten Sekunden ab.

So jetzt bin ich drauf gekommen das man ja über SUM()  eine gesamtsumme bilden kann.

Wenn ich jetzt folgendes an MySQL sende:

```
SELECT user, SUM( zeitstempel ) 
FROM zeitkarte
GROUP BY user
LIMIT 0 , 500
```
dann bekomme ich ja die Summen der Zeitstemple der Nutzer ausgeworfen.

Jetzt möchte ich aber nur einen bestimmten User zu einem bestimmten Zeitpunkt auswerfen - jetzt mache ich folgendes:

```
SELECT user, SUM( zeitstempel ) 
FROM zeitkarte
WHERE user = 'dani'
AND MONTH( kommen ) =5
AND YEAR( kommen ) =2005
GROUP BY user
LIMIT 0 , 500
```
Also User = dani im Mai 2005 das funktioniert ja auch noch.

Wie bekomme ich jetzt aber aus den zusammengezählten Sekunden - wieder eine Zeit in (evtl. tage) stunden und minuten?
Das ganze brauche ich für die Auswertung am Monatsende


----------



## hpvw (3. Mai 2005)

dwex hat gesagt.:
			
		

> Wie bekomme ich jetzt aber aus den zusammengezählten Sekunden - wieder eine Zeit in (evtl. tage) stunden und minuten?


Das geht mit der Funktion SEC_TO_TIME, die wir oben auch verwendet haben.

Außerdem möchte ich Dich noch darauf hinweisen, dass es im Sinne der Normalisierung schlechtes Datenbankdesign ist, Felder in der Datenbank zu speichern, die von anderen Feldern der Tabelle abhängig sind, sich also aus anderen Feldern berechnen (bzw. erschließen) lassen.

Folgendes Query müßte auch funktionieren, wenn Du Deinen (von kommen und gehen abhängigen) Zeitstempel weglässt:
	
	
	



```
SELECT 
user, 
SEC_TO_TIME(SUM( 
    UNIX_TIMESTAMP( gehen ) 
    - UNIX_TIMESTAMP( kommen ) ) )
    AS gesamtarbeitszeit
FROM zeitkarte
WHERE user LIKE 'dani'
AND MONTH( kommen ) =5
AND YEAR( kommen ) =2005
GROUP BY user
```
Das LIMIT ist übrigends überflüssig, wenn Du im WHERE auf einen einzelnen User beschränkst und nach diesem Wert gruppierst. Es wird nur einen Datensatz geben. Das GROUP BY wäre eigentlich auch überflüssig. Ich habe es jedoch drin gelassen, da das Query durch wegnehmen der User-Bedingung sofort auch alle User User ausspucken kann.

Für das Bilden einer Dauer mit Tagesangabe könntest Du folgendes probieren, wobei ich mir nicht sicher bin, ob es funktioniert. Gegebenenfalls könntest Du nochmal bei den Datums- und Zeitfunktionen vorbeischauen, die ich bereits verlinkt habe.
	
	
	



```
SELECT 
user, 
FROM_UNIXTIME(SUM( 
    UNIX_TIMESTAMP( gehen ) 
    - UNIX_TIMESTAMP( kommen ) ) ,
    '%e Tage %T')
    AS gesamtarbeitszeit
FROM zeitkarte
WHERE user LIKE 'dani'
AND MONTH( kommen ) =5
AND YEAR( kommen ) =2005
GROUP BY user
```

Gruß hpvw


----------



## dwex (3. Mai 2005)

Hallo,

und wieder bin ich ein kleines Stück schlauer geworden.

VIELEN VIELEN DANK!


----------



## dwex (7. Mai 2005)

Hallo,

ich habe mir deinen Hinweis auf gutes Datenbankdesign zu Herzen genommen und die ganze Geschichte umgestellt.
Jetzt tut sich mir aber wieder ein neues Problem auf.

Wenn ich nun über SQL folgendes abfrage:

```
SELECT id, user, kommen, mittaggehen, mittagkommen, gehen, (
(
UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) 
) - ( UNIX_TIMESTAMP( mittagkommen ) - UNIX_TIMESTAMP( mittaggehen ) ) 
) AS zeit
FROM `zeitkarte` 
WHERE user = 'dani'
LIMIT 0 , 30
```
dann bekomme ich auch schön alle Sekunden der jeweiligen Tage ausgerechnet.

Wenn ich aber nun Versuche mit der SUM()-Geschichte die Sekunden welche in "zeit" stehen zusammen zu zählen dann funkt das nicht.
Das ganze sieht dann so aus:

```
SELECT id, user, kommen, mittaggehen, mittagkommen, gehen, (
(
UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) 
) - ( UNIX_TIMESTAMP( mittagkommen ) - UNIX_TIMESTAMP( mittaggehen ) ) 
) AS zeit, SUM(zeit) AS zeitzusammen
FROM `zeitkarte` 
WHERE user = 'dani'
LIMIT 0 , 30
```
Ich bekomme dann die Fehlermeldung von SQL:

Unknown column 'zeit' in 'field list'

Was ich jetzt nicht verstehe - warum kommt diese Fehlermeldung - ich habe doch zuvor schon genau eben diese Spalte "zeit" definiert.

Wo liegt denn hier jetzt mein Problem?

Vielen Dank im Voraus!


----------



## hpvw (8. Mai 2005)

Das liegt an MySQL.
Da das Ausrechnen der Werte nicht von MySQL garantiert werden kann, bevor diese tatsächlich in der entgültigen Ergebnistabelle landen (interne Query-Optimierung), gelten diese Felder in weiteren Selects als undefiniert. Erst weiter hinten, bei group by, order by oder irgendwo dahinten halt, sind sie bakannt.
Du musst also den ganzen Kram, den Du für zeit ausrechnest, danach nochmal in ein sum("der kram nochmal") schreiben und dann as zeitgesamt dahinter.

Gruß hpvw


----------



## dwex (8. Mai 2005)

Hallo,

hmmm - das verstehe ich jetzt nicht so ganz.

Ich hätte das mal versucht hier so ein zu bauen wie ich es verstanden habe - das funkt aber nicht.

Oder muss ich 2 Abfragen machen - und wenn ja wie geht das denn?

Vielen Dank für deine geduldige Hilfe!


----------



## hpvw (8. Mai 2005)

```
SELECT 
id, 
user, 
kommen, 
mittaggehen, 
mittagkommen, 
gehen, 
((UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) 
) - ( UNIX_TIMESTAMP( mittagkommen ) - UNIX_TIMESTAMP( mittaggehen ) ) 
) AS zeit, 
SUM(((UNIX_TIMESTAMP( gehen ) - UNIX_TIMESTAMP( kommen ) 
) - ( UNIX_TIMESTAMP( mittagkommen ) - UNIX_TIMESTAMP( mittaggehen ) ) 
)) AS zeitzusammen
FROM `zeitkarte` 
WHERE user = 'dani'
LIMIT 0 , 30
```
So meine ich das.


----------

