Datum en tijd in MySQL [deel 1]
Inleiding
Hoe sla je datum en tijd op in je database?
Formaat van je datum en tijd
Berekenen van verschil tussen 2 data
Andere queries
Conclusie en bronnen
Inleiding
Omdat er regelmatig vragen en opmerkingen komen over data (meervoud van datum ;)) in PHP wil ik het een en ander toelichten voor
de beginnende php'ers. Dit is een korte tutorial over hoe je het goed kan doen. De snelheid van een website is van levensbelang
voor de 'houdbaarheid' van je website. Wanneer een website snel laad zullen de bezoekers sneller terug komen dan wanneer je dezelfde
informatie aanbied op een website waar je lang op moet wachten voordat hij geladen is. Een oorzaak van trage websites zijn de queries.
Beginnende scripters zullen bij queries denken: Hey ik krijg de goede resultaten dus de query klopt. Vaak word er niet aan gedacht dat
een query lang bezig is voor er informatie is. Oke, negen van de tien keer kan je in je query geen snelheidswinst halen omdat deze simpelweg
te eenvoudig is. Maar wanneer je met een vrij grote website bezig bent waarin veel queries uitgevoerd worden kan je zelfs bij simpele queries
de winst behalen. Snelheidswinst behaal je door al in je query de data te filteren die je nodig hebt en niet, zoals de meesten doen, met een
nieuwe blok PHP code. Dat betekent namelijk meer parse tijd dus langere laadtijd ;). Ik wil in deze tutorial, omdat het een vrij populair
onderwerp is, in gaan op snelheidswinst op het gebied van datum en tijd. Ook zal ik enkele andere 'handige' queries uitleggen.
 top
Hoe sla je datum en tijd op in je database?
Vaak zie je verschillende topics waarin gevraagd word hoe je bepaalde dingen kan doen met datum en tijd. In veel gevallen is deze informatie
opgeslagen in varchar formaat. Dit is niet goed. Hier kan je niet mee verder rekenen of gebruiken in je scripts. (op het echo'en na dan).
Aangeraden word om het op te slaan als date of datetime formaat. Hier kan je erg goed mee verder rekenen en voor vele doeleinden gebruiken.
Nu zal je jezelf afvragen waarom je niet timestamp hiervoor kan gebruiken. Dit is nou net het punt waar ik naar toe wil. Je moet zoveel mogelijk
de data al in je query filteren zodat je later geen extra PHP code blok nodig hebt. Met timestamp heb je altijd extra code nodig. Een quote uit het
SQL Server Books Online:
| The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. |
Verder heb je met datetime een groter bereik dan met timestamps. Namelijk 1-1-1753 tot 31-12-9999. Dit lijkt me toch wel genoeg.
top
Formaat van je datum en tijd
Wanneer je het datetime veld gebruikt zal deze altijd opgeslagen worden in de vorm YYYY-MM-DD HH:MM:SS. Hoe kan je deze waarden nou makkelijk omrekenen? Simpel zeg je. Gebruik de date functie van PHP. Dit is niet nodig.
Je kan in je query het formaat van je tijd al goed zetten. Kijk eens naar de volgende query. Deze zal ik daarna verder toelichten.
| SELECT id, datum, DATE_FORMAT(datum, '%d-%m-%Y') AS datum2 FROM datumtabel |
Wat doen we hier? We selecteren het id, de datum zoals het opgeslagen is en vervolgens de tijd in goede formaat.
Het formaat van de datum zetten we goed door de functie DATE_FORMAT Je moet wel een stukje
naar beneden scrollen hievoor. Hieronder vind je een lijstje met de letters die je kan gebruiken.
| %a | Weekday name (Sun..Sat) |
| %b | Month name (Jan..Dec) |
| %c | Month, numeric (0..12) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %f | Microseconds (000000..999999) |
| %H | Hour (00..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %i | Minutes, numeric (00..59) |
| %j | Day of year (001..366) |
| %k | Hour (0..23) |
| %l | Hour (1..12) |
| %M | Month name (January..December) |
| %m | Month, numeric (00..12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %U | Week (00..53), where Sunday is the first day of the week |
| %u | Week (00..53), where Monday is the first day of the week |
| %V | Week (01..53), where Sunday is the first day of the week; used with %X |
| %v | Week (01..53), where Monday is the first day of the week; used with %x |
| %W | Weekday name (Sunday..Saturday) |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
| %x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
| %Y | Year, numeric, four digits |
| %y | Year, numeric (two digits) |
| %% | A literal ‘%’ character |
| %x | x, for any ‘x’ not listed above |
Je kan dus het maken zoals jij wilt. :) Gebruik de functie als volgt: DATE_FORMAT(tabelnaam, letters)
Het resultaat van deze functie is een string. Gebruik deze dus niet om verder te rekenen.
Bah zal je denken. Alles in het Engels. Wil je je datum in het Nederlands zal je een stukje code moeten gebruiken. Deze kan je wel vinden in het script library. Je kan het wel in een query doen maar dat is vrij lastig.
Wil je je datum in Nederlandse 'spraakvorm' dan kan moet je de volgende code eens bekijken. Hiervoor heeft Vincent Kleijendorst (Swis.nl) een handig stukje code
op bedacht. Hij combineert PHP met SQL.
<?php
function dateFormat($sDateFormat, $sDateField, $sTaal = 'nl'){
// format een datetime veld uit mysql met de argumenten van de functie date()
$aDate = array();
$aDate['d'] = 'DATE_FORMAT(' . $sDateField . ", '%d')";
$aDate['g'] = 'DATE_FORMAT(' . $sDateField . ", '%l')";
$aDate['G'] = 'DATE_FORMAT(' . $sDateField . ", '%k')";
$aDate['h'] = 'DATE_FORMAT(' . $sDateField . ", '%h')";
$aDate['H'] = 'DATE_FORMAT(' . $sDateField . ", '%H')";
$aDate['i'] = 'DATE_FORMAT(' . $sDateField . ", '%i')";
$aDate['j'] = 'DATE_FORMAT(' . $sDateField . ", '%e')";
$aDate['m'] = 'DATE_FORMAT(' . $sDateField . ", '%m')";
$aDate['n'] = 'DATE_FORMAT(' . $sDateField . ", '%c')";
$aDate['s'] = 'DATE_FORMAT(' . $sDateField . ", '%S')";
$aDate['Y'] = 'DATE_FORMAT(' . $sDateField . ", '%Y')";
$aDate['y'] = 'DATE_FORMAT(' . $sDateField . ", '%y')";
$aDate['z'] = 'DATE_FORMAT(' . $sDateField . ", '%j')";
$aDate['w'] = 'DATE_FORMAT(' . $sDateField . ", '%w')";
$aDate['%'] = '%%';
switch ($sTaal){
case 'nl':
$aDate['D'] = 'ELT((WEEKDAY('. $sDateField .")+1),
'ma','di','wo','do','vr','za','zo')";
$aDate['F'] = 'ELT(MONTH(' . $sDateField . "),
'januari','februari','maart','april','mei','juni',
'juli','augustus','september','oktober','november',
'december')";
$aDate['l'] = 'ELT((WEEKDAY(' . $sDateField . ")+1),
'maandag','dinsdag','woensdag','donderdag','vrijdag',
'zaterdag','zondag')";
$aDate['m'] = 'ELT(MONTH(' . $sDateField . "),
'jan','feb','mrt','apr','mei','jun','jul',
'aug','sep','okt','nov','dec')";
break;
case 'en':
$aDate['D'] = 'DATE_FORMAT(' . $sDateField . ", '%a')";
$aDate['F'] = 'DATE_FORMAT(' . $sDateField . ", '%M')";
$aDate['l'] = 'DATE_FORMAT(' . $sDateField . ", '%W')";
$aDate['m'] = 'DATE_FORMAT(' . $sDateField . ", '%b')";
break;
}
$aReturn = array();
$iFormatLength = strlen($sDateFormat);
for($i = 0; $i <$iFormatLength; $i++) {
// replace argumenten per karakter
$sChar = substr($sDateFormat, $i, 1);
if (array_key_exists($sChar, $aDate)){
$aReturn[] = $aDate[$sChar];
} else {
$aReturn[] = "'" . $sChar . "'";
}
}
return 'CONCAT(' . implode(',', $aReturn) . ')';
}
?>
|
Je kan dit als volgt gebruiken:
<?php
$sql = "SELECT naam, " . dateFormat('l j F, Y', 'datum') . "
AS nl_datum FROM tabel";.
?>
|
top
Berekenen van verschil tussen 2 data
Wie kent het probleem niet? Verschil berekenen tussen twee data in je database. Velen zullen PHP hiervoor gebruiken maar MySQL heeft hier een erg
handige functie voor. Namelijk DATEDIFF(expr1,expr2). Hieronder een Engelse beschrijving van de functie die overigens alleen in MySQL gebruikt kan worden!
| DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
|
Voorbeeldje:
| SELECT id, CURRENT_DATE(), datum, DATEDIFF(CURRENT_DATE(), datum) AS verschil FROM datumtabel; |
Het resultaat komt vervolgens terug als het verschil in aantal dagen. Je kan ook TO_DAYS gebruiken maar deze manier is omslachtiger.
top
Andere handige queries
Hieronder zal ik enkele andere queries geven die best handig kunnen zijn. Heb je zelf een query die ook handig is? PM me dan zet ik die erbij :)
Wist je dat je in je query een if else structuur kan toepassen? Ik zal het hieronder uitleggen.
Eerst de link naar de officiele uitleg: MySQL Reference
Hoe gebruiken we dit nou? Ik zal eerst de query geven daarna zal ik deze verder uitleggen.
| SELECT id, betaald, CASE WHEN betaald=0 THEN 'Nee' WHEN betaald=1 THEN 'Ja' END AS betaling FROM orders; |
We produceren hier dus een overzicht van alle orders. We breiden dit uit met een kolom waarin staat of de order betaald is of niet. Vergeet de END niet hé
top
Conclusie en bronnen
Ik hoop dat het allemaal wat duidelijker is nou. Nogmaals als je ook een query hebt stuur me een message zodat ik hem kan toevoegen. Bekijk deze tutorial dus
regelmatig. Misschien dat er weer een andere query beschikbaar is ;-)
Bronnen:
Sqlteam.com
MySQL Reference
Scriptorama
top
|
Wat is jouw favoriete javascript framework? (S: 18, R: 2)
(4.42) 