Mal was zum spielen mit den vorhanden Daten...
Wobei es wirklich besser ist das Datum im Typ date zu speichern , sonst geht die Sortierung schnell in die Hose....
PHP-Code:
<?php
$sqlCreate = <<<ENDSQL
CREATE TABLE zeiten (id INTEGER PRIMARY KEY, datum TEXT, soll TEXT, ist TEXT);
INSERT INTO zeiten (datum, soll, ist) VALUES
("10.07.2017", "5:00", "2:34"),
("11.07.2017", "5:00", "5:35"),
("12.07.2017", "5:00", "4:57"),
("10.07.2017", "5:00", "1:30")
ENDSQL;
$memDB = new PDO('sqlite::memory:');
$memDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$memDB->exec($sqlCreate);
$result = $memDB->prepare('
SELECT datum, soll,
SUM(ist)-
((SUBSTR(soll,0,INSTR(soll, ":"))*60) +
(SUBSTR(soll, INSTR(soll, ":")+1, LENGTH(soll))))
AS differenz,
ABS(SUM(ist)/60)|| ":" || SUBSTR( "00" || (SUM(ist)- ABS(SUM(ist)/60)*60), -2, 2)
as ist
FROM
(SELECT datum,
soll,
(SUBSTR(ist,0,INSTR(ist, ":"))*60) +
(SUBSTR(ist, INSTR(ist, ":")+1, LENGTH(ist)))
AS ist
FROM zeiten
ORDER BY datum)
GROUP BY datum
');
$result -> execute();
$myResult = $result->fetchAll(PDO::FETCH_CLASS);
print ('<pre>');
print_r($myResult);
print ('</pre>');
Code:
Array ( [0] => stdClass Object ( [datum] => 10.07.2017 [soll] => 5:00 [differenz] => -56 [ist] => 4:04 ) [1] => stdClass Object ( [datum] => 11.07.2017 [soll] => 5:00 [differenz] => 35 [ist] => 5:35 ) [2] => stdClass Object ( [datum] => 12.07.2017 [soll] => 5:00 [differenz] => -3 [ist] => 4:57 ) )
Kommentar