Datumfuncties in MySQL

Datumfunkties in MySQL

Bewerkingen met datums zijn altijd lastig. In PHP is wel het een en ander mogelijk, maar de datumfuncties van MySQL zijn uitgebreider. Om de switch te maken van PHP naar MySQL moest er bij mij wel even een "knop om", want als je eenmaal gewend bent iets op een bepaalde manier te doen stap je niet zo snel over.

Om jullie dus over te halen de datumfuncties van MySQL eens nader te bekijken zal ik geen volledig overzicht geven, maar een paar handige praktijkvoorbeeldjes om de kracht van MySQL te demonstreren.

Er zijn vijf types voor datum en tijd:

DATETIME - YYYY-MM-DD HH:MM:SS
TIMESTAMP - YYYYMMDDHHMMSS
DATE - YYYY-MM-DD
TIME - HH:MM:SS
YEAR - YYYY

Als je niet weet welk type je het beste kunt gebruiken, raad ik DATETIME aan. Daar kun je alle kanten mee op.

Meteen al een voordeel in MySQL: het bereik. Een DATE veld mag van '1000-01-01' tot '9999-12-31' lopen. Dus niet vanaf 1970 tot 2038 zoals bij sommige PHP functies. Voor het vullen van een veld kun je een string aanmaken in het goede format, maar het is handiger om ook dat door MySQL te laten doen. In de volgende voorbeelden gebruik ik voor het overzicht steeds DATETIME.

Voorbeeld 1: De huidige datumtijd invullen
<?php
$sql = "
INSERT INTO tabel
(datumtijdveld)
VALUES
(NOW())
";
?>

Wat vaak voorkomt is dat je niet een hele datumtijd wilt hebben, maar een anders geformatteerde. Daarvoor heb je de functie DATE_FORMAT. Net zoals er conversie specifiers zijn voor strftime in PHP heb je die voor DATE_FORMAT in MySQL. Zie het MySQL Manual voor een overzicht.

Voorbeeld 2: Een datumtijd in format YYYY-MM-DD
<?php
$sql = "
SELECT DATE_FORMAT(datumtijdveld, '%Y-%m-%d') AS datum
FROM tabel
";
?>

Geeft alleen het datumdeel van een DATETIME, bijv 2005-12-05

Voorbeeld 3: Een datumtijd in format HH:MM:SS
<?php
$sql = "
SELECT DATE_FORMAT(datumtijdveld, '%H-%i-%s') AS tijd
FROM tabel
";
?>

Geeft alleen het tijddeel van een DATETIME, bijv 13:29:30

Je kunt ook allerlei andere handige dingen doen, zoals dag van de week (%w, zondag = 0, zaterdag = 6), weeknummer (%u, %U, %v of %V, afhankelijk of je met 0 wilt beginnen of met 1 en of je de week met zondag of maandag wilt beginnen)

Op verzoek van Sebastiaan: als je echt niet zonder UNIX timestamp kunt, kun je gemakkelijk een DATETIME naar een UNIX timestamp omzetten met strtotime(). Ik raad je echter aan, dat niet te doen. Ga maar eens met een UNIX timestamp in een statistiekentabel uitrekenen hoeveel bezoekers per dag je de afgelopen maand had :-)

Er zijn ook specifiers voor datumtijd als tekst, maar die zijn helaas allemaal in het Engels. Als je %b gebruikt, krijg je de afgekorte maandnaam (Jan ... Dec) en met %M de volledige (January ... December). MySQL kent bij mijn weten geen set_locale, maar die van PHP werkt ook niet altijd en overal. Als je dus graag dingen wilt laten zien als "vrijdag 11 mei 2006" moet je twee arrays aanmaken met weekdagnamen en maandnamen.

In PHP gebruiken we hiervoor setlocale (LC_ALL, 'nl_NL'); Helaas werkt die niet op alle servers.

Voorbeeld 4: Nederlandse notatie voor dagen en maanden
<?php
$weekdagnamen = array (zondag, maandag, dinsdag, woensdag, donderdag, vrijdag, zaterdag);
$maandnamen = array (1 => januari, februari, maart, april, mei, juni, juli, augustus, september, oktober, november, december);

$sql = "
SELECT
DATE_FORMAT(datumtijdveld, '%w') AS dag,
DATE_FORMAT(datumtijdveld, '%d') AS datum,
DATE_FORMAT(datumtijdveld, '%c') AS maand,
DATE_FORMAT(datumtijdveld, '%Y') AS jaar
FROM tabel
ORDER BY datumtijdveld
";
$res = mysql_query ($sql) or die (mysql_error ());

while ($obj = mysql_fetch_object ($res)) {
echo '<p>' . $weekdagnamen[$obj->dag] . ' ' . $obj->datum . ' ' . $maandnamen[$obj->maand] . ' ' . $obj->jaar . '</p>';
}
?>

Als je het niet erg vindt dat de notatie in het Engels is, wordt de query veel simpeler.

Voorbeeld 5: Engelse notatie voor dagen en maanden

<?php
$sql = "
SELECT
DATE_FORMAT(datumtijdveld, '%w %d %c %Y') AS datum
FROM tabel
ORDER BY datumtijdveld
";
$res = mysql_query ($sql) or die (mysql_error ());

while ($obj = mysql_fetch_object ($res)) {
echo '<p>' . $obj->datum . '</p>';
}
?>

MySQL kan ook rekenen met datumtijd velden.
Stel dat je een factureersysteem hebt en dat je wilt weten wanneer de betalingstermijn van een factuur verloopt.

Voorbeeld 6: Facturen laten zien waarvan de betalingstermijn (4 weken) verlopen is.

<?php
$sql = "
SELECT factuurnummer
FROM facturen
WHERE DATE_ADD(factuurdatum, INTERVAL 28 DAY) < NOW()
AND status = 'nog niet betaald'
";
?>

In plaats van DAY kun je ook werken met HOUR, MINUTE, SECOND, MONTH en YEAR. Er zijn ook combinaties mogelijk, zoals DAY_HOUR, DAY_MINUTE, DAY_SECOND enzovoorts.

Zo zou je, omwille van het voorbeeld, de betalingstermijn op 4 weken plus een halve dag kunnen zetten.
<?php
$sql = "
SELECT factuurnummer
FROM facturen
WHERE DATE_ADD(factuurdatum, INTERVAL '28 12' DAY_HOUR) < NOW()
AND status = 'nog niet betaald'
";
?>

Voorbeeld 7: Wie moet ik een verjaardagskaartje sturen?
EDIT: DIT VOORBEELD WERKT NIET GOED. IK BEN OP ZOEK NAAR EEN MOOI ALTERNATIEF

<?php
$sql = "
SELECT
naam,
DATE_FORMAT(geboortedatum, '%d') AS datum
FROM kennissen
WHERE (geboortedatum - NOW()) BETWEEN 1 AND 3
";
?>

Ik hoop dat ik je een beetje op het spoor heb gezet van de mogelijkheden van datumtijd-funkties in MySQL. Deze tut is allesbehalve compleet. Voor verdere info verwijs ik graag naar het MySQL manual. Deze link is expres niet naar de nieuwste versie, want de meeste hosts draaien nog geen MySQL 5.