MySQL bemoeit zich met de tijd

[MadEgg]

Ik heb een PHP-scriptje die samenwerkt met een MySQL database om wat dingetjes op te slaan.
Deze wil ik graag van een tijd voorzien, en in verband met tijdzones en makkelijke converteerbaarheid enzo wil ik in de database graag de tijden in UTC hebben, in een datetime kolom.

Dit is allemaal goed en wel, maar als ik de tijd er vervolgens weer uit wil halen met een UNIX_TIMESTAMP formaat zit die daarmee te klooien om het aan de tijdzone van de server in te stellen.

Voorbeeldje:
in een kolom zit deze waarden:

1970-1-1 00:00:00

Dit is het epoch, zou dus een timestamp van 0 moeten opleveren.
UNIX_TIMESTAMP hierop levert ook 0 op. So far so good.

1970-1-1 00:00:10 levert echter nog steeds 0 op. Ik krijg het vermoeden dat MySQL geen negatieve timestamps retourneert.

1970-1-1 00:01:10 levert met UNIX_TIMESTAMP 10 op. Daar gaat het dus fout. Hij zit alweer te corrigeren met de UTC+1 offset van de server.

En dat wil ik niet. Ik wil dat het epoch gewoon netjes 0 oplevert, ik reken zelf in mijn script de tijdzones en dergelijke er wel bij. Kan ik dat voor elkaar krijgen?

[Booster]

Zover ik weet zijn timestamps niet onderhevig aan tijdzones. Het resultaat zou dus altijd hetzelfde moeten zijn, ongeacht welke tijdzone er staat ingesteld (al is het GMT+7 bij wijze van)

Pas bij de weergave van een normale datum wordt er iets met tijdzones gedaan.

[ Voor 18% gewijzigd door Booster op 14-05-2004 16:31 ]

[MadEgg]

Daar heb je ook gelijk in. Een Unix Timestamp is het aantal seconden sinds het in de GMT/UTC tijdzone 1 januari 1970 12 uur AM was, onafhankelijk van in welke tijdzone je zit.

Het probleem treedt al eerder op, bij de conversie van de datetime kolom naar de timestamp.

MySQL gaat er blijkbaar vanuit dat de datum die in de datetime kolom is opgeslagen, in de lokale tijd is, in plaats van in GMT/UTC. Daardoor wordt er een uur minder(of 2 uur tijdens zomertijd) gerekend omdat NL 1(of 2 bij zomertijd) uur voorloopt op GMT/UTC.

Het is mijn bedoeling dat MySQL doorheeft dat de datum en tijd in de datetime kolom een UTC-datum is, en niet een van de lokale tijdzone.

[ Voor 13% gewijzigd door MadEgg op 14-05-2004 20:17 ]

[Olaf van der Spek]

Welk field type gebruik je en hoe insert je die fields?

[MadEgg]

Een 'datetime' field zoals ik al eerder had gezegd, en de inhoud heb ik voor deze tests met behulp van phpMyAdmin ingevoerd m.b.h. van insert new row, en vervolgens '1970-1-1 00:00:00' daar intypen.

De timestamp haal ik er vervolgens weer uit met behulp van het SQL-venster van phpMyAdmin en het volgende commando: 'select id, UNIX_TIMESTAMP(tijd) from test;'.

[Booster]

Vreemd. Heb hier even een testje gedaan met MySQL 4.0.18 en phpMyAdmin 2.5.6.

code:

select UNIX_TIMESTAMP('1970-01-01 01:00:01')

levert inderdaad een '1' op als timestamp. Alles daarvoor een 0.

Timezone van de server is CEST, wat in principe UTC+2/GMT+2 is.
SQL timezone rapporteerd ook CEST.

code:

booster@cerebral:~$ mysql -V
mysql  Ver 12.22 Distrib 4.0.18, for pc-linux (i686)
booster@cerebral:~$ \
> date; \
> date --utc; \
> date +%s
Sat May 15 15:47:54 CEST 2004
Sat May 15 13:47:54 UTC 2004
1084628874
booster@cerebral:~$

Als ik vervolgens die datum in MySQL omzet naar een UNIX_TIMESTAMP:

code:

SELECT UNIX_TIMESTAMP('2004-05-15 13:47:54')

dan krijg ik 1084628874 terug.

Het is dus wél consistent met de timestamp die ik krijg van het systeem.
Een verklaring kan ik op het moment nog niet geven, maar misschien als ik het even laat bezinken

[ Voor 9% gewijzigd door Booster op 15-05-2004 16:51 ]

[MadEgg]

Jouw CEST tijd en UTC tijd zijn gelijk volgens date?

Sat May 15 13:47:54 CEST 2004
Sat May 15 13:47:54 UTC 2004

Zou juist

Sat May 15 13:47:54 CEST 2004
Sat May 15 11:47:54 UTC 2004

of toch

Sat May 15 15:47:54 CEST 2004
Sat May 15 13:47:54 UTC 2004

moeten zijn... Toch?

[Booster]

Ah, zie je wel. Had 1x verkeerd gedrukt. Paste ipv copy. (per ongeluk 15:47:54 overschreven)

Nu gecorrigeerd Het stond wel goed in mijn console, zoals jij al voordeed.

[ Voor 14% gewijzigd door Booster op 15-05-2004 16:57 ]

[MadEgg]

Hmm.

Schijnbaar dus wel maf gedrag van MySQL. Zal daar eens informeren naar een UTC_DATETIME field of iets dergelijks.

Moet ik in plaats daarvan maar gewoon char(10) gebruiken, moet nog wel even vooruit kunnen, zo'n 285 jaar

[Booster]

MadEgg schreef op 15 mei 2004 @ 19:44:
Hmm.

Schijnbaar dus wel maf gedrag van MySQL. Zal daar eens informeren naar een UTC_DATETIME field of iets dergelijks.

Well, als het consistent is met de timestamp die het systeem afgeeft dan lijkt me het geen mysql quirk toch?

BTW, nog een (mogelijk) foutje in jouw openingspost:

00:01:10

Ik denk dat je hier 01:00:10 bedoeld?
Anders zou jouw systeem timestamp 10 afgeven na 1 minuut en 10 seconden. Dat is geen UTC+1

[MadEgg]

Nee, sorry, je hebt gelijk! My bad!

Het was inderdaad 1 seconde na 1 uur na 't epoch.

't is geen quirk, het zal wel een reden hebben maar ik vind het wel erg kortzichtig gedacht van de MySQL mensen dat er alleen maar tijden in de lokale tijd opgeslagen zouden worden in een datetime veld.
Tenminste UTC als alternatief bieden vind ik geen slecht plan.

[Olaf van der Spek]

In MySQL 4.1 is UTC een alternatief dacht ik.

[MadEgg]

OlafvdSpek schreef op 15 mei 2004 @ 22:03:
In MySQL 4.1 is UTC een alternatief dacht ik.

Hoe dan? Ik kan er niets over vinden op google/mysql.com...

[Olaf van der Spek]

http://dev.mysql.com/doc/...e_and_time_functions.html

MySQL is inderdaad een beetje irritant (qua tijdzones).
Insert data met from_unixtime(), select data met unix_timestamp() en gebruik verder geen MySQL date/time functies en het gaat goed.

http://bugs.mysql.com/bug.php?id=1776

Per connection timezone support is in our short-term TODO and moreover it is
being implemented now. It will appear in one of not so distant versions of MySQL
(but I can't tell now would it be in 4.1 branch or in 5.0).

[ Voor 96% gewijzigd door Olaf van der Spek op 16-05-2004 14:26 ]

[MadEgg]

OlafvdSpek schreef op 16 mei 2004 @ 14:15:
http://dev.mysql.com/doc/...e_and_time_functions.html

MySQL is inderdaad een beetje irritant (qua tijdzones).
Insert data met from_unixtime(), select data met unix_timestamp() en gebruik verder geen MySQL date/time functies en het gaat goed.

http://bugs.mysql.com/bug.php?id=1776


[...]

MAW, gebruik de datetime fields gewoon niet.
Zonder from_unixtime en unix_timestamp in een char(10) field ofzo invoegen is dan nog makkelijker. Ik heb sowieso geen tijd-functies nodig van MySQL.