[SQL] tijdrekenen in sql query

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Rudie dirkx

rudie dirkx

08/12/2007 13:37:00
Quote Anchor link
heb een tabel `activities` met de volgende relevante kolommen:
- 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
 
PHP hulp

PHP hulp

05/02/2023 04:30:47
 
Joren de Wit

Joren de Wit

08/12/2007 13:51:00
Quote Anchor link
Ik neem aan dat er in een pauze ook geen nieuwe activiteit gepland kan worden, dus heb je te maken met een begin en eindtijd van een serie slots.

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)
PHP script in nieuw venster Selecteer het PHP script
1
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

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
 
Rudie dirkx

rudie dirkx

08/12/2007 14:01:00
Quote Anchor link
Vooral
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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!
 
Rudie dirkx

rudie dirkx

08/12/2007 14:55:00
Quote Anchor link
Het is trouwens fout :)
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)
PHP script in nieuw venster Selecteer het PHP script
1
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) );";


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
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.