Tutorials
Datum- en tijdfuncties in MySQL
Datum- en tijdfunctie in MySQL nader bekeken
Pagina 1
Inleiding
Het rekenen met data en tijden in PHP is lang niet alles. Sterker nog, de mogelijkheid tot rekenen met data en tijden in PHP is heel beperkt. De enige optie is het omzetten van de data en tijden naar timestamps, om er vervolgens berekeningen mee uit te voeren. Maar ook dan loop je tegen problemen aan als je bijvoorbeeld wilt weten of je komende maandag tussen 11 en 12 uur 's ochtends een afspraak hebt.
In een database is het rekenen met data en tijden een stuk eenvoudiger. MySQL biedt je een hoop datum- en tijdfunctie om allerlei selecties te maken en berekeningen uit te voeren.
Op het forum zie ik nog steeds veel posts van mensen die de databasefuncties nog niet ontdekt hebben en die nog vast houden aan het rekenen met PHP. Sinds de laatste tutorial over dit onderwerp uit december 2005 zijn er een aantal dingen veranderd. In deze tutorial zal ik proberen een overzicht te geven van de belangrijkste datum- en tijdfuncties die MySQL je nu te bieden heeft.
In deze tutorial ga ik er vanuit dat je de basis van MySQL al onder de knie hebt. Ik zal in voorbeelden alleen de gebruikte SQL queries tonen, het mag dan ook voor zichzelf spreken dat deze queries nog uitgevoerd moeten worden in een PHP script.
Benodigde voorkennis
- Basis PHP
- Werken met een database in PHP
- Basis SQL
In een database is het rekenen met data en tijden een stuk eenvoudiger. MySQL biedt je een hoop datum- en tijdfunctie om allerlei selecties te maken en berekeningen uit te voeren.
Op het forum zie ik nog steeds veel posts van mensen die de databasefuncties nog niet ontdekt hebben en die nog vast houden aan het rekenen met PHP. Sinds de laatste tutorial over dit onderwerp uit december 2005 zijn er een aantal dingen veranderd. In deze tutorial zal ik proberen een overzicht te geven van de belangrijkste datum- en tijdfuncties die MySQL je nu te bieden heeft.
In deze tutorial ga ik er vanuit dat je de basis van MySQL al onder de knie hebt. Ik zal in voorbeelden alleen de gebruikte SQL queries tonen, het mag dan ook voor zichzelf spreken dat deze queries nog uitgevoerd moeten worden in een PHP script.
Benodigde voorkennis
- Basis PHP
- Werken met een database in PHP
- Basis SQL
Pagina 2
Datum- en tijdtypes
In MySQL zijn er 5 datatypes die geschikt zijn om data en/of tijden in op te slaan:
DATE – 'YYYY-MM-DD'
TIME – 'HH:MM:SS'
DATETIME – 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP – 'YYYYMMDDHHMMSS'
YEAR – 'YYYY'
Van deze 5 zul je het type TIMESTAMP (bijna) nooit gebruiken aangezien je met het type DATETIME beter uit de voeten kunt. Verder spreekt het lijkt me voor zich dat je een datum opslaat in een DATE veld, een tijd in een TIME veld en een combinatie van die twee, een tijdstip, in een DATETIME veld.
Meteen komen we hier al een voordeel van MySQL t.o.v PHP tegen: het bereik van de velden. In een DATE veld kun je data kwijt die lopen van 1000-01-01 tot 9999-12-31 terwijl je in een TIME veld tijden kwijt kunt van -838:59:59 tot 838:59:59.
Een belangrijk feit om hier ook op te merken, is dat een TIME in MySQL dus niet gebonden is aan de 24 uur van een dag en dus niet gelijk is aan een tijdstip. Een DATETIME veld heeft namelijk een bereik van 1000-01-01 00:00:00 tot 9999-12-31 23:59:59.
Dit alles in tegenstelling tot het bereik van timestamps in PHP die lopen van 13-12-1901 20:45:54 tot 19-01-2038 03:14:07. En bij PHP<5 ligt de ondergrens zelfs op 01-01-1970.
In deze tutorial zal ik een aantal kolomnamen gebruiken. De namen komen overeen met de volgende datatypes:
datumveld – DATE
tijdveld – TIME
datumtijdveld – DATETIME
DATE – 'YYYY-MM-DD'
TIME – 'HH:MM:SS'
DATETIME – 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP – 'YYYYMMDDHHMMSS'
YEAR – 'YYYY'
Van deze 5 zul je het type TIMESTAMP (bijna) nooit gebruiken aangezien je met het type DATETIME beter uit de voeten kunt. Verder spreekt het lijkt me voor zich dat je een datum opslaat in een DATE veld, een tijd in een TIME veld en een combinatie van die twee, een tijdstip, in een DATETIME veld.
Meteen komen we hier al een voordeel van MySQL t.o.v PHP tegen: het bereik van de velden. In een DATE veld kun je data kwijt die lopen van 1000-01-01 tot 9999-12-31 terwijl je in een TIME veld tijden kwijt kunt van -838:59:59 tot 838:59:59.
Een belangrijk feit om hier ook op te merken, is dat een TIME in MySQL dus niet gebonden is aan de 24 uur van een dag en dus niet gelijk is aan een tijdstip. Een DATETIME veld heeft namelijk een bereik van 1000-01-01 00:00:00 tot 9999-12-31 23:59:59.
Dit alles in tegenstelling tot het bereik van timestamps in PHP die lopen van 13-12-1901 20:45:54 tot 19-01-2038 03:14:07. En bij PHP<5 ligt de ondergrens zelfs op 01-01-1970.
In deze tutorial zal ik een aantal kolomnamen gebruiken. De namen komen overeen met de volgende datatypes:
datumveld – DATE
tijdveld – TIME
datumtijdveld – DATETIME
Pagina 3
Invoegen van data en tijden
Bij het invoegen van data en tijden in de database zijn er een aantal dingen waar je rekening mee moet houden. Allereerst moet je ervoor zorgen dat je het juiste datatype gebruikt. Een tijd invoegen in een DATE veld gaat natuurlijk niet werken. Ook is het niet echt nuttig om een DATETIME veld te gebruiken als je alleen een datum op wilt slaan.
Verder is het van belang dat je bij het invoegen let op het formaat van de datum of tijd. Gegevens horen ingevoegd te worden in het standaardformaat van het type veld waarin ze geplaatst worden. Een datum in een DATE veld zal dus altijd in het ‘YYYY-MM-DD’ formaat ingevoegd moeten worden. MySQL accepteert bij uitzondering ook formaten als YYYYMMDD of YYYY/MM/DD als DATE of YYYYMMDDHHMMSS als DATETIME, maar het is verstandig om je gewoon aan het standaard formaat te houden.
Voorbeeld 1: Invoegen van een datum
Om de huidige datum in te voegen is het verstandiger om gebruik te maken van de functie CURDATE().
Voorbeeld 2: Invoegen van de huidige datum met CURDATE()
Deze functie is echter niet bruikbaar als we het huidige tijdstip in zouden willen voegen. CURDATE() geeft namelijk alleen een datum. Voor het invoegen van het huidige tijdstip kunnen we de functie NOW() gebruiken.
Voorbeeld 3: Invoegen van het huidige tijdstip met NOW()
Aangezien NOW() een heel tijdstip terug geeft, is het ook mogelijk om met deze functie een DATE of TIME in te voegen. In dat geval wordt alleen het benodigde gedeelte van de functie gebruikt.
Verder is het van belang dat je bij het invoegen let op het formaat van de datum of tijd. Gegevens horen ingevoegd te worden in het standaardformaat van het type veld waarin ze geplaatst worden. Een datum in een DATE veld zal dus altijd in het ‘YYYY-MM-DD’ formaat ingevoegd moeten worden. MySQL accepteert bij uitzondering ook formaten als YYYYMMDD of YYYY/MM/DD als DATE of YYYYMMDDHHMMSS als DATETIME, maar het is verstandig om je gewoon aan het standaard formaat te houden.
Voorbeeld 1: Invoegen van een datum
INSERT INTO tabel (datumveld)
VALUES ('2007-07-10')
Om de huidige datum in te voegen is het verstandiger om gebruik te maken van de functie CURDATE().
Voorbeeld 2: Invoegen van de huidige datum met CURDATE()
INSERT INTO tabel (datumveld)
VALUES (CURDATE())
Deze functie is echter niet bruikbaar als we het huidige tijdstip in zouden willen voegen. CURDATE() geeft namelijk alleen een datum. Voor het invoegen van het huidige tijdstip kunnen we de functie NOW() gebruiken.
Voorbeeld 3: Invoegen van het huidige tijdstip met NOW()
INSERT INTO tabel (datumtijdveld)
VALUES (NOW())
Aangezien NOW() een heel tijdstip terug geeft, is het ook mogelijk om met deze functie een DATE of TIME in te voegen. In dat geval wordt alleen het benodigde gedeelte van de functie gebruikt.
Pagina 4
Uitlezen van data en tijden
Het uitlezen van data en tijden uit de database gaat precies hetzelfde als uitlezen van andere gegevens. Het zal echter vaak voorkomen dat het standaardformaat van je datum, tijd of tijdstip eigenlijk niet gewenst is en dat je een ander formaat zou willen. Voor dit doeleinde kunnen we de functie DATE_FORMAT() gebruiken.
Voorbeeld 4: Het ophalen van een datum in het DD-MM-YYYY formaat
Dit voorbeeld is toegepast op een DATE veld, maar hetzelfde werkt natuurlijk voor een DATETIME veld. In dat geval wordt alleen het datum gedeelte van de DATETIME geselecteerd.
Let op: gebruik voor de alias niet dezelfde naam als de kolomnaam. Dit kan problemen opleveren.
Hetzelfde valt toe te passen op een TIME veld, alleen zullen er dan wel andere 'specifiers' opgegeven moeten worden.
Voorbeeld 5: Het ophalen van een tijd in het HH:MM formaat
Het formaat waarin een datum, tijd of tijdstip opgehaald wordt, staat natuurlijk niet vast. Stel dat ik bijvoorbeeld een gastenboek heb en dat ik de datum en tijd van een bericht geformatteerd uit de database wil halen. Ook daar is DATE_FORMAT() voor te gebruiken.
Voorbeeld 6: Het ophalen van een geformatteerde datum/tijd aanduiding
Dit zal resulteren in iets als 'Geplaatst op 10-07-2007 om 14:06 uur'.
Naast de in deze voorbeelden getoonde opties, zijn er nog veel meer andere specifiers om een datum, tijd of tijdstip te formatteren. Een volledige lijst met specifiers is te vinden in de MySQL handleiding.
Voorbeeld 4: Het ophalen van een datum in het DD-MM-YYYY formaat
SELECT DATE_FORMAT(datumveld, '%d-%m-%Y') AS datum
FROM tabel
Dit voorbeeld is toegepast op een DATE veld, maar hetzelfde werkt natuurlijk voor een DATETIME veld. In dat geval wordt alleen het datum gedeelte van de DATETIME geselecteerd.
Let op: gebruik voor de alias niet dezelfde naam als de kolomnaam. Dit kan problemen opleveren.
Hetzelfde valt toe te passen op een TIME veld, alleen zullen er dan wel andere 'specifiers' opgegeven moeten worden.
Voorbeeld 5: Het ophalen van een tijd in het HH:MM formaat
SELECT DATE_FORMAT(tijdveld, '%H:%i') AS tijd
FROM tabel
Het formaat waarin een datum, tijd of tijdstip opgehaald wordt, staat natuurlijk niet vast. Stel dat ik bijvoorbeeld een gastenboek heb en dat ik de datum en tijd van een bericht geformatteerd uit de database wil halen. Ook daar is DATE_FORMAT() voor te gebruiken.
Voorbeeld 6: Het ophalen van een geformatteerde datum/tijd aanduiding
SELECT DATE_FORMAT(datumtijdveld, 'Geplaatst op %d-%m-%Y om %H:%i uur.') AS tijdstip
FROM tabel
Dit zal resulteren in iets als 'Geplaatst op 10-07-2007 om 14:06 uur'.
Naast de in deze voorbeelden getoonde opties, zijn er nog veel meer andere specifiers om een datum, tijd of tijdstip te formatteren. Een volledige lijst met specifiers is te vinden in de MySQL handleiding.
Pagina 5
Functies voor het weergeven van specifieke info
Naast de DATE_FORMAT() functie die we op de vorige pagina gezien hebben, zijn er nog veel meer functies die gegevens over een datum, tijd of tijdstip weer kunnen geven. De belangrijkste daarvan zijn:
DATE() – geeft het datum gedeelte van een DATETIME
DAYOFMONTH() – geeft de dag van de maand (1-31)
DAYOFWEEK() – geeft de dag van de week (1 = Zondag, 2 = Maandag, etc.)
DAYOFYEAR() – geeft de dag van het jaar (1-366)
HOUR() – geeft het uur van een TIME of DATETIME
MINUTE() – geeft de minuut van een TIME of DATETIME
MONTH() – geeft de maand van het jaar
TIME() – geeft het tijd gedeelte van een DATETIME
WEEKOFYEAR() – geeft de week van het jaar
YEAR() – geeft het huidige jaar
Een aantal voorbeelden om het gebruik van deze functies te verduidelijken.
Voorbeeld 7: Selecteer het datum gedeelte van een DATETIME
Resultaat: 2007-07-10
Voorbeeld 8: Selecteer het tijdgedeelte van een DATETIME
Resultaat: 14:06:32
Voorbeeld 9: Selecteer de huidige maand
Deze query geeft de huidige maand terug.
Voorbeeld 10: Selecteer huidige weekdag
Dit zal resulteren in de index van de huidige weekdag. Hierbij zal een 1 voor zondag staan, een 2 voor maandag, enz...
Een volledige lijst van alle datum- en tijdfuncties vind je in de MySQL handleiding.
DATE() – geeft het datum gedeelte van een DATETIME
DAYOFMONTH() – geeft de dag van de maand (1-31)
DAYOFWEEK() – geeft de dag van de week (1 = Zondag, 2 = Maandag, etc.)
DAYOFYEAR() – geeft de dag van het jaar (1-366)
HOUR() – geeft het uur van een TIME of DATETIME
MINUTE() – geeft de minuut van een TIME of DATETIME
MONTH() – geeft de maand van het jaar
TIME() – geeft het tijd gedeelte van een DATETIME
WEEKOFYEAR() – geeft de week van het jaar
YEAR() – geeft het huidige jaar
Een aantal voorbeelden om het gebruik van deze functies te verduidelijken.
Voorbeeld 7: Selecteer het datum gedeelte van een DATETIME
SELECT DATE('2007-07-10 14:06:32') AS datum
FROM tabel
Resultaat: 2007-07-10
Voorbeeld 8: Selecteer het tijdgedeelte van een DATETIME
SELECT TIME('2007-07-10 14:06:32') AS tijd
FROM tabel
Resultaat: 14:06:32
Voorbeeld 9: Selecteer de huidige maand
SELECT MONTH(NOW()) AS maand
FROM tabel
Deze query geeft de huidige maand terug.
Voorbeeld 10: Selecteer huidige weekdag
SELECT DAYOFWEEK(NOW()) AS weekdag
FROM tabel
Dit zal resulteren in de index van de huidige weekdag. Hierbij zal een 1 voor zondag staan, een 2 voor maandag, enz...
Een volledige lijst van alle datum- en tijdfuncties vind je in de MySQL handleiding.
Pagina 6
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
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
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.
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)
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
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
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)
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
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)
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.
+ 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
SELECT datumveld + INTERVAL 3 WEEK AS datum
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
SELECT DAYOFWEEK(NOW() - INTERVAL 3 13 DAY_HOUR) AS weekdag
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.
DELETE FROM tabel
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)
DELETE FROM tabel
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
SELECT factuur
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
SELECT naam
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)
SELECT naam
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
SELECT afspraak
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)
SELECT afspraak
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.
Pagina 7
Data in Engels of Nederlands formaat
Zoals al aangegeven in het hoofdstuk over het uitlezen van de data uit de database, is het mogelijk om je data in een eigen formaat te zetten. Nu zijn er ook specifiers die de dag en maand als complete tekst weer kunnen geven.
Voorbeeld 20: Weergeven van de datum in het Engels.
Dit zal de datum weergeven als 'Tuesday July 10th 2007'. Natuurlijk kun je hier zelf nog mee spelen.
Voor het weergeven van de datum in het Nederlands, kun je proberen de local settings van de database te wijzigen. Deze setting is pas beschikbaar in versies 4.1.21, 5.0.25 en 5.1.12. Bij eerdere versies uit de 4.x, 5.0.x en 5.1.x series is dit dus geen optie. Kijk voor een alternatieve oplossing naar voorbeeld 22 of 23.
Voorbeeld 21: Weergeven van de datum in het Nederlands
Deze query gebruik je om de locale settings te wijzigen. Vervolgens worden de namen van dagen en maanden met DATE_FORMAT() nu in het nederlands weergegeven.
De weergave zou nu als volgt moeten zijn: 'dinsdag 10 juli 2007'. (Voor meer informatie over deze optie, zie de MySQL handleiding.)
Mocht het op deze manier niet lukken, dan zul je iets meer moeten doen. Er zijn dan twee oplossingen over. In de eerste wordt wel gebruik gemaakt van wat PHP maar wellicht is deze beter te begrijpen dan de tweede.
Voorbeeld 22: Weergeven van de datum in het Nederlands met PHP.
Het formaat waarin de datum nu komt te staan is bijvoorbeeld: 'dinsdag 10 juli 2007'.
Maar het kan ook alleen met de database, en dat is eigenlijk waar deze hele tutorial wel om draait. Zo min mogelijk met PHP werken als het gaat om data en tijden.
Voorbeeld 23: Weergeven van de datum in het Nederlands met MySQL
In deze query maken we gebruik van ELT() om de functie van de array’s uit de PHP code te vervangen en CONCAT() om alles aan elkaar te plakken. Eigenlijk werkt het precies hetzelfde en bovendien is het een stuk korter.
Voorbeeld 20: Weergeven van de datum in het Engels.
SELECT DATE_FORMAT(datumtijdveld, '%W %M %D %Y') AS datum
FROM tabel
Dit zal de datum weergeven als 'Tuesday July 10th 2007'. Natuurlijk kun je hier zelf nog mee spelen.
Voor het weergeven van de datum in het Nederlands, kun je proberen de local settings van de database te wijzigen. Deze setting is pas beschikbaar in versies 4.1.21, 5.0.25 en 5.1.12. Bij eerdere versies uit de 4.x, 5.0.x en 5.1.x series is dit dus geen optie. Kijk voor een alternatieve oplossing naar voorbeeld 22 of 23.
Voorbeeld 21: Weergeven van de datum in het Nederlands
SET lc_time_names = 'nl_NL'
Deze query gebruik je om de locale settings te wijzigen. Vervolgens worden de namen van dagen en maanden met DATE_FORMAT() nu in het nederlands weergegeven.
SELECT DATE_FORMAT(datumtijdveld, '%W %d %M %Y') AS datum
FROM tabel
De weergave zou nu als volgt moeten zijn: 'dinsdag 10 juli 2007'. (Voor meer informatie over deze optie, zie de MySQL handleiding.)
Mocht het op deze manier niet lukken, dan zul je iets meer moeten doen. Er zijn dan twee oplossingen over. In de eerste wordt wel gebruik gemaakt van wat PHP maar wellicht is deze beter te begrijpen dan de tweede.
Voorbeeld 22: Weergeven van de datum in het Nederlands met PHP.
<?php
// Arrays gebruikt voor het weergeven van Nederlandse namen
$weekdagen = array('zondag', 'maandag', 'dinsdag', 'woensdag', 'donderdag', 'vrijdag', 'zaterdag');
$maanden = array('januari', 'februari', 'maart', 'april', 'mei', 'juni', 'juli', 'augustus', 'september', 'oktober', 'november', 'december');
// SQL query om benodigde gegevens uit de database te halen.
$sql = "
SELECT
DAYOFWEEK(datumveld) AS weekdag,
MONTH(datumveld) AS maand,
DAYOFMONTH(datumveld) AS dag
YEAR(datumveld) AS jaar
FROM
tabel
";
// Contorle of query gelukt is en eventueel foutafhandeling toepassen.
if(!$res = mysql_query($sql))
{
trigger_error(mysql_errno().': '.mysql_error());
}
else
{
// Fetchen van gegevens en combineren tot resultaat
$row = mysql_fetch_assoc($res);
$datum = $weekdagen[$row['weekdag']].' '.$row['dag'].' '.$maandag[$row['maand']].' '.$row['jaar'];
}
?>
Het formaat waarin de datum nu komt te staan is bijvoorbeeld: 'dinsdag 10 juli 2007'.
Maar het kan ook alleen met de database, en dat is eigenlijk waar deze hele tutorial wel om draait. Zo min mogelijk met PHP werken als het gaat om data en tijden.
Voorbeeld 23: Weergeven van de datum in het Nederlands met MySQL
SELECT CONCAT(
ELT(DAYOFWEEK(datumveld), 'zondag', 'maandag', 'dinsdag', 'woensdag', 'donderdag', 'vrijdag', 'zaterdag'),
' ',
DAYOFMONTH(datumveld),
' ',
ELT(MONTH(datumveld), 'januari', 'februari', 'maart', 'april', 'mei', 'juni', 'juli', 'augustus', 'september', 'oktober', 'november', 'december'),
' ',
YEAR(datumveld)
) AS datum_NL
FROM tabel
In deze query maken we gebruik van ELT() om de functie van de array’s uit de PHP code te vervangen en CONCAT() om alles aan elkaar te plakken. Eigenlijk werkt het precies hetzelfde en bovendien is het een stuk korter.
Pagina 8
Slotwoord en referenties
Tot zover deze tutorial over het gebruik van de datum- en tijdfuncties van MySQL. Hoewel een ieder natuurlijk voor zichzelf moet kiezen waarmee hij wil rekenen als het om data en tijden gaat, hoop ik dat ik met deze tutorial de grote kracht van de database een stuk verduidelijkt heb. En misschien dat mensen met deze tutorial over de streep getrokken worden en toch afzien van het gebruik van PHP in dit opzicht, want in mijn opinie is dat telkens weer het wiel opnieuw uitvinden.
Deze tutorial kan gezien worden als vervolg of uitbreiding op een oudere tutorial door Jan Koehoorn uit december 2005 over dit onderwerp.
Reacties, kritiek en gevonden fouten zijn natuurlijk altijd welkom.
Deze tutorial is ook hier te vinden.
De volgende referenties en bronnen horen nog toegevoegd te worden:
- Datumfuncties in MySQL door Jan Koehoorn
- MySQL 5.0 Reference Manual
- Verschillende topics op het PHPHulp forum.
Deze tutorial kan gezien worden als vervolg of uitbreiding op een oudere tutorial door Jan Koehoorn uit december 2005 over dit onderwerp.
Reacties, kritiek en gevonden fouten zijn natuurlijk altijd welkom.
Deze tutorial is ook hier te vinden.
De volgende referenties en bronnen horen nog toegevoegd te worden:
- Datumfuncties in MySQL door Jan Koehoorn
- MySQL 5.0 Reference Manual
- Verschillende topics op het PHPHulp forum.
Reacties
0