[SQL] tijdrekenen in sql query
- start_time (TIME)
- duration (INT)
- repeat (INT)
- pause_slots (INT)
`duration` heeft de eenheid 'slots', net als `pause_slots`. Hoe lang een slot is, ligt aan de eigenaar van de activity. Kan 10 min zijn, of 15, of 30, etc. Een bekend getal in minuten in ieder geval.
`repeat` is (logisch!?) het getal dat aangeeft hoevaak de activity wordt herhaald, en `pause_slots` is hoeveel er tussen elke herhaling zit. De eerste keer dat het uitgevoerd wordt, is ook een 'herhaling', dus als `repeat` 1 is, wordt de activity 1 keer uitgevoerd, niet 2.
Er bestaan meerdere activities voor 1 eigenaar. Je moet de eigenaar zien als een timeline, opgedeeld in 'slots'. Elke 10 min bijv is dus een nieuwe slot. En elke 10 minuten wordt gekeken of er een activity begint op dat moment. Omdat je voor 8 activities (repeat = 8) waar van de 1e begint om 10:00, maar 1 start_time weet (10:00) en de andere 7 niet, heb ik in php er een loopje voor gemaakt, zodat in een array alle 8 begin en eindtijden hebt staan. Dat werkt dus.
Maar nu ontstaat er een probleem. Ik wil met 1 query nagaan, of er een activity bezig is ergens tussen tijd A en tijd B, maar de enige TIME die ik weet, is de start time van de eerste activity van de set. Stel je voor:
slotsize = 10 minuten voor deze eigenaar
activity: start_time = 10:00, duration = 5 slots, pause = 1 slot, repeat = 4 keer
Er zal dus van 13:00 - 13:50 een activity bezig zijn -> dat stukje tijd is dus bezet. Er kan voor heel die periode NIETS anders opgeslagen worden. Maar hoe kom ik daar achter, zonder een php loop met veel controles??
Is er in MySQL 4 iets om met tijden en minuten optellen te rekenen? Misschien is het dan mogelijk om met `start_time`, `repeat` en de 'slotsize' een soort-van-array te maken in SQL die je met IN kan vergelijken met de start of eind tijd van je nieuwe activity...
Iemand ervaring met tijden optellen en meerdere waarden vergelijken in 1 query?
Misschien is het wel gewoon niet mogelijk, maar als het wel mogelijk is, is het sneller in SQL (lijkt me) dan in PHP.
Mijn dank is groot.
tv
Gewijzigd op 01/01/1970 01:00:00 door Rudie dirkx
De complete serie uit je voorbeeld begint dus om 10.00 uur en is om 14.00 uur afgelopen. Die 14.00 uur weet je echter nog niet omdat dat een uitkomst van een berekening is.
Nu wil je weten of er een activity bezig is tussen tijd A en tijd B, of anders: of tijd A of tijd B tussen de begin en eindtijd van een serie liggen. De query om een eventuele activity tussen tijd A en B op te halen, zou er dan als volgt uit komen te zien:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT
activity,
start_time + INTERVAL ((duration + pause) * repeat * $1) MINUTE AS end_time
FROM
activities
WHERE
$2 BETWEEN start_time AND end_time
OR
$3 BETWEEN start_time AND end_time
activity,
start_time + INTERVAL ((duration + pause) * repeat * $1) MINUTE AS end_time
FROM
activities
WHERE
$2 BETWEEN start_time AND end_time
OR
$3 BETWEEN start_time AND end_time
Waarbij de volgende variabelen uit een php script komen
- $1: slotsize (minuten)
- $2: tijd A (formaat: hh:ii:ss)
- $3: tijd B (formaat: hh:ii:ss)
Gewijzigd op 01/01/1970 01:00:00 door Joren de Wit
Code (php)
1
start_time + INTERVAL ((duration + pause) * repeat * $1) MINUTE AS end_time
is wat ik zocht :) Geweldig! Bedankt.
Het is eigenlijk wel zo :( dat er activiteiten gepland moeten kunnen worden in pauzes... Je kan namelijk zeggen dat een pauze 3 uur is en een activiteit in die set 1 uur duurt (dus het totaal 1+3+1+3+1 uur duurt als je `repeat` 3 is). In die 3 uur pauze zouden dan 3 x 1 uur activiteiten moeten kunnen o.i.d.
Maar als ik nu zeg dat het niet kan, kan het niet :) Dus ik denk dat het dat wordt.
Blanche, enorm bedankt!
Je kan een 'gemaakte' kolom niet vergelijken in een WHERE clause. En je kan een bestaande (niet geselecteerde) kolom niet vergelijken in een HAVING clause.
Dus mijn query is nu zo:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$szSqlQuery = '
SELECT
a.start_time,
ADDTIME(
a.start_time,
SEC_TO_TIME( s.slotsize*60 * (a.length_in_slots+a.pause_slots_in_between) * a.repeat_times - (s.slotsize*60*a.pause_slots_in_between) )
) AS end_time
FROM
club_sports s,
resources r,
class_activities a
WHERE
a.resource_id = r.id AND
r.club_sport_id = s.id AND
a.attach_to_resource_id IS NOT NULL AND
a.attach_to_resource_id = '.$f_iResource." AND
a.start_date <= '".$f_szDate."' AND
( a.end_date >= '".$f_szDate."' OR a.end_date IS NULL ) AND
a.is_enabled = '1' AND
a.on_days LIKE '%".$iToday."%'
HAVING
( ('".$f_szStartTime."' BETWEEN start_time AND end_time) OR ('".$f_szEndTime."' BETWEEN start_time AND end_time) );";
SELECT
a.start_time,
ADDTIME(
a.start_time,
SEC_TO_TIME( s.slotsize*60 * (a.length_in_slots+a.pause_slots_in_between) * a.repeat_times - (s.slotsize*60*a.pause_slots_in_between) )
) AS end_time
FROM
club_sports s,
resources r,
class_activities a
WHERE
a.resource_id = r.id AND
r.club_sport_id = s.id AND
a.attach_to_resource_id IS NOT NULL AND
a.attach_to_resource_id = '.$f_iResource." AND
a.start_date <= '".$f_szDate."' AND
( a.end_date >= '".$f_szDate."' OR a.end_date IS NULL ) AND
a.is_enabled = '1' AND
a.on_days LIKE '%".$iToday."%'
HAVING
( ('".$f_szStartTime."' BETWEEN start_time AND end_time) OR ('".$f_szEndTime."' BETWEEN start_time AND end_time) );";
Edit:
Met INTERVAL lukte het me trouwens niet.... Sql bleef errors geven, dus heb t met ADDTIME en SEC_TO_TIME gedaan :)
Edit:
Gebruik voor `on_days` een SET met de waarden 0-6 voor de weekdagen zondag-maandag. Wat is het makkelijkst/snelst controleren voor SQL? Gebruik nu LIKE '%$iToday%', maar misschien kan het beter?
Gewijzigd op 01/01/1970 01:00:00 door rudie dirkx