Rekenen met data en tijden
Het grootste voordeel van MySQL ten opzichte van PHP is het rekenen met data en tijden. De belangrijkste functies die we hier gebruiken zijn:
+ operator - Het optellen van een bepaald interval bij een tijd, tijdstip of datum
- operator - Het aftreken van een bepaald interval van een tijd, tijdstip of datum
DATEDIFF() - Berekent het verschil in dagen tussen twee tijdstippen of data.
TIMEDIFF() - Berekent het verschil in tijd tussen twee tijden of tijdstippen.
Voorbeeld 11: Tel 3 weken bij een datum op
Code (php)
1
2
2
SELECT datumveld + INTERVAL 3 WEEK AS datum
FROM tabel
FROM tabel
Voor het interval kun Je naast WEEK ook nog maten als HOUR, DAY, MONTH en YEAR gebruiken. Een volledige lijst vind je wederom in de MySQL handleiding.
Ook is het mogelijk om samengestelde intervallen te gebruiken. Stel dat we bijvoorbeeld willen weten wat voor dag het 3 dagen en 13 uur geleden was.
Voorbeeld 12: Selecteer weekdag van 3 dagen en 13 uur geleden
Code (php)
1
2
2
SELECT DAYOFWEEK(NOW() - INTERVAL 3 13 DAY_HOUR) AS weekdag
FROM tabel
FROM tabel
Nu is het natuurlijk ook mogelijk om dit soort berekeningen in een WHERE clause van je query op te nemen. Stel dat we alle records willen verwijderen die ouder zijn dan 1 jaar.
Voorbeeld 13: Verwijder alle records ouder dan een jaar.
Code (php)
1
2
2
DELETE FROM tabel
WHERE datumveld + INTERVAL 1 YEAR < CURDATE()
WHERE datumveld + INTERVAL 1 YEAR < CURDATE()
Hier wordt eerst 1 jaar bij de datum van een record opgeteld en dan vergeleken met de huidige datum. Als die waarde kleiner is dan de huidige datum is dat record dus meer dan een jaar oud.
Deze query hadden echter ook op een andere manier kunnen aanpakken. Hiervoor hadden we ook een DATEDIFF() functie kunnen gebruiken:
Voorbeeld 14: Verwijder alle records ouder dan een jaar (2)
Code (php)
1
2
2
DELETE FROM tabel
WHERE DATEDIFF(CURDATE(), datumveld) > 365
WHERE DATEDIFF(CURDATE(), datumveld) > 365
De DATEDIFF() functie geeft het verschil tussen de huidige datum en de datum van het record weer in dagen. Als dat groter is dan 365 is het record dus meer dan een jaar oud.
Voorbeeld 15: Selecteer alle openstaande facturen die ouder zijn dan 2 weken
Code (php)
1
2
3
4
2
3
4
SELECT factuur
FROM facturen
WHERE datumveld < CURDATE() - INTERVAL 2 WEEK
AND betaald = 0
FROM facturen
WHERE datumveld < CURDATE() - INTERVAL 2 WEEK
AND betaald = 0
Deze query geeft een overzicht van alle facturen die ten eerste nog openstaan (betaald = 0) en ten tweede ouder zijn dan 2 weken.
Stel nu dat we bezig zijn met een online/offline systeem voor onze gebruikers. We willen weten welke gebruikers in de laatste 5 minuten nog actief geweest zijn. In onze 'gebruikers' tabel hebben we daartoe een kolom 'last_active' van het type DATETIME.
Voorbeeld 16: Selecteer alle gebruikers die de laatste 5 minuten actief waren
Code (php)
1
2
3
2
3
SELECT naam
FROM gebruikers
WHERE last_active + INTERVAL 5 MINUTE > NOW()
FROM gebruikers
WHERE last_active + INTERVAL 5 MINUTE > NOW()
Hier gebruiken we NOW() omdat we hier te maken hebben met een DATETIME. We tellen 5 minuten bij de waarde van last_active op en vergelijken die met het huidige tijdstip. Als die waarde groter is, is de gebruiker de laatste 5 minuten actief geweest.
Een andere oplossing voor deze query is het gebruik van TIMEDIFF().
Voorbeeld 17: Selecteer alle gebruikers die de laatste 5 minuten actief waren (2)
Code (php)
1
2
3
4
2
3
4
SELECT naam
FROM gebruikers
WHERE MINUTE(TIMEDIFF(NOW(), last_active)) < 5
AND HOUR(TIMEDIFF(NOW(), last_active)) = 0
FROM gebruikers
WHERE MINUTE(TIMEDIFF(NOW(), last_active)) < 5
AND HOUR(TIMEDIFF(NOW(), last_active)) = 0
De TIMEDIFF() functie geeft het verschil in tijd terug tussen het huidige moment en de waarde van last_active. Aangezien we nu dus te maken hebben met een gegeven in het TIME formaat gebruiken we MINUTE() om het aantal minuten te bepalen.
Daarnaast moeten we ook controleren of het verschil in uren wel 0 is want MINUTE() selecteert alleen het minuten gedeelte en resulteert dus in dezelfde waarde voor bijvoorbeeld '00:05:38' en '02:05:43'.
Tenslotte nog een voorbeeld waar ik het in de inleiding al over had: het selecteren van de afspraken die komende maandag tussen 11 en 12 uur 's ochtends plaatsvinden. Daarvoor hebben we een tabel 'agenda' met daarin een kolom 'tijdstip' van het type DATETIME.
Voorbeeld 18: Selecteer afspraken komende maandag tussen 11:00 en 12:00 uur
Code (php)
1
2
3
2
3
SELECT afspraak
FROM agenda
WHERE tijdstip BETWEEN '2007-07-15 11:00:00' AND '2007-07-15 12:00:00'
FROM agenda
WHERE tijdstip BETWEEN '2007-07-15 11:00:00' AND '2007-07-15 12:00:00'
In deze query gebruiken we de absolute data voor komende maandag, dus alle afspraken van maandag 15-7-2007 tussen 11 en 12 worden geselecteerd.
Het is echter ook mogelijk om een query te maken die elke week geldig is:
Voorbeeld 19: Selecteer afspraken komende maandag tussen 11:00 en 12:00 uur (2)
Code (php)
1
2
3
4
2
3
4
SELECT afspraak
FROM agenda
WHERE TIME(tijdstip) BETWEEN '11:00:00' AND '12:00:00'
AND DATEDIFF(NOW(), tijdstip) BETWEEN 0 AND 6
FROM agenda
WHERE TIME(tijdstip) BETWEEN '11:00:00' AND '12:00:00'
AND DATEDIFF(NOW(), tijdstip) BETWEEN 0 AND 6
Allereerst zijn we natuurlijk alleen geinteresseerd in de afspraken die plaatsvinden tussen 11 en 12 uur. We gebruiken TIME() om het tijdgedeelte van het tijdstip te selecteren en vervolgens controleren we of dat tussen 11 en 12 uur valt.
Dan zijn we natuurlijk ook alleen geinteresseerd in de eerstvolgende maandag. Met DATEDIFF() controleren we daar of het verschil in dagen met de datum van vandaag tussen 0 en 6 ligt.
Dit is maar en beperkt aantal voorbeelden, maar ik denk dat ze wel laten zien wat je met MySQL allemaal kunt. Voor een volledige lijst van functies kijk je in de MySQL handleiding.
« vorige pagina | volgende pagina »
Inhoudsopgave
- Inleiding
- Datum- en tijdtypes
- Invoegen van data en tijden
- Uitlezen van data en tijden
- Functies voor het weergeven van specifieke info
- Rekenen met data en tijden
- Data in Engels of Nederlands formaat
- Slotwoord en referenties