Als je werkt met datum/tijd velden in MySQL ligt een DATETIME type voor de hand. Om een DATETIME veld te vullen vanuit PHP kun je eenvoudig een iso-formaat als string (‘YYYY-MM-DD HH:II:SS’) gebruiken. In de meeste gevallen voldoet dat prima. Het wordt alleen lastig als je informatie uit verschillende tijdzones komt. Of wat als alle informatie uit 1 tijdzone komt maar een periode overspant waarbinnen de klok werd verzet?
Binnen een code-omgeving (PHP/Java/JavaScript) heb je meestal wel de mogelijkheid om te rekenen met datum/tijd objecten die van zichzelf weten over welke tijdzone het gaat en of het zomertijd was of niet. Meestal werken zulke objecten onder water met een unix-timestamp en wat meta-informatie over tijdzone en zomertijd. Maar hoe zit dat in MySQL?
Je kunt natuurlijk een integer-veld in MySQL maken en daarin de unix timestamp opslaan. Dan weet je zeker dat het het juiste tijdstip is (als het wegvallen van de tijdzone-informatie acceptabel is) en sorteren op die kolom gaat ook goed. Maar een unix timestamp is niet echt lekker leesbaar. Dan liever een echte datum/tijd kolom. Maar gaat dat altijd goed?
MySQL kent 2 mogelijkheden voor het opslaan van een datum/tijd in een enkel veld. Het kan door middel van een DATETIME en een TIMESTAMP kolomtype. Een DATETIME kolom kun je zien als een tekstueel veld. De waarde wordt opgeslagen hoe je het er in stopt en wordt ook weer zo weergegeven als hoe je het er in stopte. Een TIMESTAMP kolom werkt iets anders; bij het invoegen wordt de waarde geconverteerd naar een unix timestamp en bij het weergeven wordt deze waarde weer naar een tekstuele representatie omgezet.
Het verschil in werking zie je als je een tabel aanmaakt met zowel een DATETIME en een TIMESTAMP kolom:
CREATE TABLE `test_date` (
`timestamp_column` timestamp NULL DEFAULT NULL,
`datetime_column` datetime NULL
);
Vervolgens voegen we 3 tijdstippen in die elk een uur na elkaar plaatsvinden. Niet geheel toevallig kiezen we die rond het moment dat de klok een uur terug gaat (in de nacht van 24 oktober op 25 oktober 2015) en wel om 01:30, 02:30 en weer een uur later om 02:30 als de klok dus een uur terug is. Om er zeker van te zijn dat we die tijdstippen aanduiden geven we MySQL alleen waarden in een timestamp-notatie:
INSERT INTO test_date VALUES (FROM_UNIXTIME(1445729400), FROM_UNIXTIME(1445729400));
INSERT INTO test_date VALUES (FROM_UNIXTIME(1445729400 + 3600), FROM_UNIXTIME(1445729400 + 3600));
INSERT INTO test_date VALUES (FROM_UNIXTIME(1445729400 + 7200), FROM_UNIXTIME(1445729400 + 7200));
Als we kijken hoe MySQL dit heeft opgeslagen halen we de waarden weer op:
SELECT timestamp_column, datetime_column FROM test_date;
2015-10-25 01:30:00 2015-10-25 01:30:00
2015-10-25 02:30:00 2015-10-25 02:30:00
2015-10-25 02:30:00 2015-10-25 02:30:00
Dat lijkt er prima uit te zien: de tijdstippen kloppen allemaal. Als we echter de waarden terugrekenen naar unix timestamps (zoals we ze er in gestopt hebben) blijkt dat het niet meer helemaal klopt. Door gebruik te maken van de UNIX_TIMESTAMP() bij een TIMESTAMP kolom wordt de feitelijk opgeslagen waarde gebruikt dus kun je precies zien hoe die is opgeslagen. Bij de DATETIME kolom vindt er opnieuw conversie plaats (maar daarvan wisten we al hoe hij was opgeslagen).
SELECT UNIX_TIMESTAMP(timestamp_column), UNIX_TIMESTAMP(datetime_column) FROM test_date;
1445729400 1445729400
1445736600 1445736600
1445736600 1445736600
Wat is er mis gegaan? Je zou verwachten dat in ieder geval de TIMESTAMP notatie toch wel de juiste unix timestamp zou moeten hebben? Die kan onder water immers met timestamps werken, dachten we.
Het is mis gegaan bij het invoegen; daar wordt gebruik gemaakt van de FROM_UNIXTIME() functie die de ingevoerde unix timestamp omzet naar een ‘tekstuele’ representatie en maakt daarbij gebruik van de huidige tijdzone van de server. Als die hetzelfde is als van het tijdstip dat je probeert in te voegen gaat het goed, maar aangezien we deze query tijdens de zomertijd uitvoeren, wordt bij het terugrekenen van de tekstuele representatie naar een timestamp gewerkt met de zomertijd. En dus met het moment van voor het omzetten van de klok. Vandaar dat de laatste 2 rijen hetzelfde worden opgeslagen.
Om het derde tijdstip (in ieder geval in de TIMESTAMP kolom) juist op te slaan moeten we MySQL tijdelijk een andere tijdzone laten gebruiken:
SET time_zone=’+02:00′;
INSERT INTO test_date (timestamp_column, datetime_column) VALUES (FROM_UNIXTIME(1445729400), FROM_UNIXTIME(1445729400));
INSERT INTO test_date (timestamp_column, datetime_column) VALUES (FROM_UNIXTIME(1445729400 + 3600), FROM_UNIXTIME(1445729400 + 3600));
Alsof de klok een uur terug is gezet (om 03:00):
SET time_zone=’+01:00′;
INSERT INTO test_date (timestamp_column, datetime_column) VALUES (FROM_UNIXTIME(1445729400 + 7200), FROM_UNIXTIME(1445729400 + 7200));
Als we nu de waarden ophalen als tekstuele versie is er niks veranderd en lijken de waarden ook weer gewoon te kloppen:
SELECT timestamp_column, datetime_column FROM test_date;
2015-10-25 01:30:00 2015-10-25 01:30:00
2015-10-25 02:30:00 2015-10-25 02:30:00
2015-10-25 02:30:00 2015-10-25 02:30:00
Maar als we nu de waarden ophalen als unix timestamps zie je ineens het verschil tussen de TIMESTAMP en de DATETIME kolom:
SELECT UNIX_TIMESTAMP(timestamp_column), UNIX_TIMESTAMP(datetime_column) FROM test_date;
1445729400 1445729400
1445733000 1445736600
1445736600 1445736600
Daarmee lijkt het nuttig om voor datum/tijd velden waar rekenwerk mee plaats moet vinden altijd een TIMESTAMP kolom te gebruiken. Er zijn echter situaties waar dat niet helemaal werkt; als je in 1 statement meerdere waarden wilt meegeven worden ze geacht allemaal in dezelfde tijdzone (en zomertijd of niet) te zitten anders werkt het alsnog niet. En vergeet het beperkte bereik van een TIMESTAMP kolom niet; deze accepteert alleen waarden tussen 1970 en 2038.
Hoewel niet echt goed leesbaar zou ik er dan toch voor kiezen om de unix timestamp in een INT kolom te gaan opslaan. Maak er dan wel een UNSIGNED INT van, anders krijg je al snel een overflow.
Gebruik maken van een INT zorgt er in ieder geval voor dat het voor 100% duidelijk is welke datum/tijd waarde het is. En je kunt er veilig op sorteren en mee rekenen, ook binnen MySQL. En voor de leesbaarheid kun je natuurlijk ook met FROM_UNIXTIME() spieken wat je hebt opgeslagen.