SQL met tijd als variabele
Google bood ook geen hulp.
Het gaat om het volgende:
Ik heb een table "schedules". Deze bevat in ieder geval de TIME kolommen "start" en "end". De start en end vertegenwoordigen het start en eindtijdstip van een bepaalde procedure. Werking hiervan is verder niet van belang.
Stel start is 10:00 en end is 22:00
Het tijdstip is nu 9:23. Als ik dan de query uitvoer met als filter: start<=tijdstip and end>tijdstip. Dan krijg ik zoals verwacht geen resultaat.
Is het tijdstip nu 13:14 dan krijg ik wel resultaat etc.
Dit is simpel en werkt wel. Maar ik loop tegen een probleem aan als het eindtijdstip over de nacht gaat, bijvoorbeeld: start is 22:00 en end is 02:00.
Nu werkt de filter start<=tijdstip and end>tijdstip niet meer. Want als het 23:11 is dan is start inderdaad kleiner maar hij ziet end natuurlijk ook als kleiner. Dus krijg ik geen resultaat.
Wie kan mij op weg helpen om hier toch mee om te kunnen gaan?
in dat laatste geval zou je 2015-10-15 22:00:00 en 2015-10-16 04:00 moeten opslaan
Ookal gebruik je de datum niet php kan deze wel gebruiken om bijvoorbeeld tijden te zoeken in de toekomst of verleden.
Code (php)
1
2
3
4
2
3
4
SELECT *
FROM tabel
WHERE NOW() BETWEEN starttijd AND eindtijd
OR (TIMEDIFF(starttijd, eindtijd) < 0 AND NOW() BETWEEN eindtijd AND starttijd);
FROM tabel
WHERE NOW() BETWEEN starttijd AND eindtijd
OR (TIMEDIFF(starttijd, eindtijd) < 0 AND NOW() BETWEEN eindtijd AND starttijd);
ik denk bij nader inzien dat de laatste between weg mag
(start<=tijdstip and end>tijdstip)
or
(end<start and (start<=tijdstip or end>tijdstip))
?
@Ward, Ik snap gedeeltelijk wat je bedoeld. Zou je die iets meer toe kunnen lichten?
starttijd + duur = eindtijd
Met zo'n formule met twee bekenden kun je altijd de derde onbekende afleiden, want:
• eindtijd = starttijd + duur
• starttijd = eindtijd - duur
• duur = eindtijd - starttijd
Daarmee kun je het probleem dus omdenken. Je kunt een TIME-veld namelijk niet alleen gebruiken om een vast tijdstip op te slaan, maar ook voor een tijdsduur in uren-minuten-seconden. Sla je bijvoorbeeld de starttijd 23:11:00 op en een duur van 2:00:00 voor 2 uur, dan wordt de eindtijd 25:11:00. Vervolgens hoef je alleen de weergave aan te passen, volgens een eenvoudige logica: alles groter dan 24 uur is na middernacht dus morgen (of overmorgen, of overovermorgen, enzovoort).
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT *
FROM tabel
WHERE
(eindtijd >= starttijd and NOW() BETWEEN starttijd AND eindtijd)
OR
(eindtijd < starttijd and ( NOW() >= starttijd OR NOW() <= eindtijd ) )
;
FROM tabel
WHERE
(eindtijd >= starttijd and NOW() BETWEEN starttijd AND eindtijd)
OR
(eindtijd < starttijd and ( NOW() >= starttijd OR NOW() <= eindtijd ) )
;
Voor BETWEEN geldt, dat als x BEWTEEN a AND b
dat a kleiner is dan b
Code (php)
1
SELECT 5 BETWEEN 6 AND 4, 5 BETWEEN 4 AND 6
geeft 0 en 1
dus daarop hoef je niet apart te controleren.
ik begrijp dat het strikt genomen niet noodzakelijk is, maar het leek me wel verhelderend om eindtijd met starttijd te vergelijken aan beide kanten van de OR. Zeker als je zo'n statement over een jaar of wat nog eens leest. Dan is duidelijk dat je in het ene geval test op eindtijd na starttijd en in het andere geval op eindtijd voor starttijd.
Je moet nu ook elke keer de eindtijd uitrekenen. Als je vaak vragen krijgt als:
"Wanneer is X afgelopen?"
Dan moet je altijd antwoorden met:
"Euh, A plus B uur"
Wanneer de duur niet echt relevant is of als je deze zelden nodig hebt dan zou ik deze niet introduceren. Daarnaast is dit gegeven afleidbaar (net zoals de eindtijd afleidbaar is in de relatief opgezette oplossing). Ik zou je oplossing kiezen aan de hand van wat je vaker (rechtstreeks) gebruikt: duur of eindtijd.
Daarnaast klint het alsof er hier:
Quote:
Het werken met een datum heeft niet mijn voorkeur. Als ik dagelijks op bepaalde tijden de procedure wil laten uitvoeren dan zou ik tot in eeuwigheid de tijden met datum moeten gaan vullen. Vandaar dat het een TIME veld is.
Een probleem opgelost moet worden, daar hangt nu de opzet van je database van af? Die blijkbaar niet handig in het gebruik is omdat je daar vragen over stelt.
Denk niet alleen goed na hoe je iets oplost, maar ook waar en waarom. En wat dat vervolgens voor consequenties heeft.
De data wordt vanaf extern geladen in de database. Hier kan ik niets aan veranderen. Ja er zijn meerdere wegen die naar rome leiden. Maar in dit geval is de snelste als ik het rechtstreeks uit de database kan halen.
@Jan Thanks. Dit werkt helemaal... Als ik hem zo bekijk had ik het eigenlijk zelf ook wel kunnen bedenken... Maar bedankt!
Gewijzigd op 15/10/2015 17:16:30 door Sander Z
Dit lijkt mij een bepalende factor bij het opstellen van een oplossing.
Je zegt hier enkel over:
Quote:
Werking hiervan is verder niet van belang.
Ik denk dat zojuist het tegendeel is bewezen?
Oplossingen worden aangedragen op grond van aangeleverde informatie. Als niet voldoende informatie wordt aangeleverd kan ook geen fatsoenlijke oplossing worden voorgesteld.
Daarbij zorgt dit voor verspilling van tijd.
Gewijzigd op 15/10/2015 17:15:44 door Thomas van den Heuvel
De database kan ik wel degelijk aanpassen. Maar ik zou in dit geval niet weten waarom. De geladen data is alleen een tijdsstempel en staat compleet los van een datum. Die input kan gewijzigd worden maar zal niets meer opleveren gezien alle benodigde data geladen wordt.
Ik ben er verder al uit.
Ik ben het wel met je eens dat je vaker wat verder moet kijken wat nu eigenlijk de oplossing is ;)
Ik zou dan geen oplossing zoeken die daar op voortborduurt, maar het oorspronkelijke probleem wegnemen.
Het eerste is toch een beetje symptoombestrijding.
Code (php)
1
2
2
... WHERE NOW() BETWEEN CURRENT_DATE + INTERVAL start_time HOUR_SECOND
AND CURRENT_DATE + INTERVAL end_time HOUR_SECOND + INTERVAL end_time < start_time DAY
AND CURRENT_DATE + INTERVAL end_time HOUR_SECOND + INTERVAL end_time < start_time DAY
Of
Code (php)
1
2
2
... WHERE CURRENT_TIME BETWEEN start_time AND
IF(end_time > start_time, end_time, TIME_ADD(end_time, '24:00:00'))
IF(end_time > start_time, end_time, TIME_ADD(end_time, '24:00:00'))
Toevoeging op 16/10/2015 08:56:35:
Bij nader inzien is alleen de eerste methode correct.