Udregning af tid i MySQL

Det er ikke ligefrem den slags beregninger SQL er god til (altså beregning foretaget på to på hindanden følgende rækker).

På en eller anden måde må vi have de to rækker placeret "ved siden af hindanden".

Det kan man evt. gøre sådan her (men optimalt er det ikke!):

SELECT t1.cellenummer, t1.status AS frastatus, t2.status AS tilstatus, t1.timestamp AS fratimestamp, t2.timestamp AS tiltimestamp
FROM tabel AS t1 LEFT JOIN tabel AS t2 ON t1.cellenummer = t2.cellenummer
WHERE t2.timestamp IS NULL OR t2.timestamp = (SELECT MIN(timestamp) FROM tabel WHERE timestamp > t1.timestamp AND cellenummer = t1.cellenummer)

Det burde give et resultatsæt som dette:
1, 'SETUP', 'BUSY', 2006-01-01 13:00:00, 2006-01-01 13:30:00
1, 'BUSY', 'PAUSE', 2006-01-01 13:30:00, 2006-01-01 15:00:00
1, 'PAUSE', 'BUSY', 2006-01-01 15:00:00, 2006-01-01 15:15:00
osv.

Det kan man så arbejde videre med, idet man kan gruppere på cellenummer og frastatus og så summere på forskellen mellem fratimestamp og tiltimestamp.

I alt bliver det så til en SQL sætning som ca. lyder sådan:

SELECT cellenummer, frastatus AS status, SUM(tiltimestamp - fratimestamp)
FROM (
SELECT t1.cellenummer, t1.status AS frastatus, t2.status AS tilstatus, t1.timestamp AS fratimestamp, t2.timestamp AS tiltimestamp
FROM tabel AS t1 LEFT JOIN tabel AS t2 ON t1.cellenummer = t2.cellenummer
WHERE t2.timestamp IS NULL OR t2.timestamp = (SELECT MIN(timestamp) FROM tabel WHERE timestamp > t1.timestamp AND cellenummer = t1.cellenummer)
) AS temp
GROUP BY cellenummer, frastatus


Ovenstående er ikke ment som en færdig løsning, da jeg ikke helt har styr på MySQLs syntax, og ikke har testet det, men forhåbentlig kan det give dig inspiration til en mulig løsning... :-)

Hej kjulius

Jeg arbejder sammen med Hotdp på dette. Vi kommer nok først til at teste på Mandag, så vender vi tilbage til dig. Men mange tak for det hurtige svar :)

Hej kjulius

Det du har lavet ser ud til at virke helt som det skal
(www.bygge-steen.dk/diagram1.jpg).
Men er det muligt at regne ud hvad forskellen er mellem de ændrede status er(fratimestamp til tiltimestamp)? Hvis det er kan du godt smide det som svar for så er pointene dine! hvis ikke det kan lade sig gøre kan du også smide et svar så skal du nok også få point :)
Mvh David

Jeg har konsulteret en MySQL manual, og funktionen TIMEDIFF skulle kunne klare det.

Altså:

SELECT cellenummer, frastatus AS status, SUM(tidsforbrug) as totalttidsforbrug
FROM (
SELECT t1.cellenummer, t1.status AS frastatus, t2.status AS tilstatus, t1.timestamp AS fratimestamp, t2.timestamp AS tiltimestamp, TIMEDIFF(t2.timestamp, t1.timestamp) AS tidsforbrug
FROM tabel AS t1 LEFT JOIN tabel AS t2 ON t1.cellenummer = t2.cellenummer
WHERE t2.timestamp IS NULL OR t2.timestamp = (SELECT MIN(timestamp) FROM tabel WHERE timestamp > t1.timestamp AND cellenummer = t1.cellenummer)
) AS temp
GROUP BY cellenummer, frastatus

Funktionen blev dog først indført i version 4.1.1, så hvis det er en ældre udgave af MySQL I bruger, kunne man måske bruge et alternativ, nemlig at bruge UNIX_TIMESTAMP funktionen, som vil returnere antallet af sekunder siden 1. januar 1970 (vil virke til og med  31. december 2037).

SELECT cellenummer, frastatus AS status, SUM(sekundtidsforbrug) as totalttidsforbrug
FROM (
SELECT t1.cellenummer, t1.status AS frastatus, t2.status AS tilstatus, t1.timestamp AS fratimestamp, t2.timestamp AS tiltimestamp, UNIX_TIMESTAMP(t2.timestamp) - UNIX_TIMESTAMP(t1.timestamp) AS sekundtidsforbrug
FROM tabel AS t1 LEFT JOIN tabel AS t2 ON t1.cellenummer = t2.cellenummer
WHERE t2.timestamp IS NULL OR t2.timestamp = (SELECT MIN(timestamp) FROM tabel WHERE timestamp > t1.timestamp AND cellenummer = t1.cellenummer)
) AS temp
GROUP BY cellenummer, frastatus

Det vil returnere tidsforbruget i sekunder.

Jeg håber jeg har fået det rigtigt, da jeg ikke kan teste det...

Fra version 5.0.0 har man også mulighed for at bruge funktionen TIMESTAMPDIFF.

SELECT cellenummer, frastatus AS status, SUM(tidsforbrug) as totalttidsforbrug
FROM (
SELECT t1.cellenummer, t1.status AS frastatus, t2.status AS tilstatus, t1.timestamp AS fratimestamp, t2.timestamp AS tiltimestamp, TIMESTAMPDIFF(MINUTE, t2.timestamp, t1.timestamp) AS tidsforbrug
FROM tabel AS t1 LEFT JOIN tabel AS t2 ON t1.cellenummer = t2.cellenummer
WHERE t2.timestamp IS NULL OR t2.timestamp = (SELECT MIN(timestamp) FROM tabel WHERE timestamp > t1.timestamp AND cellenummer = t1.cellenummer)
) AS temp
GROUP BY cellenummer, frastatus

TIMESTAMPDIFF er en funktion, som kan returnere forskellen i flere forskellige enheder, hvoraf jeg har valgt MINUTE - altså differencen i minutter. Andre muligheder er FRAC_SECOND, SECOND, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.