Keskustelu: Nettisivujen teko: Päivämäärät MySQL:ssä

Onko olemassa keinoa/asetusta, millä päivämäärätiedon voisi aada ja tallentaa MySQL-tietokantaan suomalaisessa muodossa ja suomalaisessa ajassa?

Olen kokeillut strftime() ja gmdate()-funktioita pelkän ajan tulostamiseen, mutta setlocale ja oletus aikaalueen asetuksista ei ole ollut hyötyä. gmdate antaa ajan kaksi tuntia liian aikaisin.

Nyt taitaa taas olla jokin vakava puute perustiedoissa. Normaalioloissa sinun ei tarvitse millään tavalla huomioida aikavyöhykettä, eli "suomalainen aika" ei eroa millään tavalla muista ajoista. Päivämäärän ei myöskään kuulu olla tietokannassa "suomalaisessa muodossa", vaan kaikki käyttäjälle näytettävä muotoilu tehdään PHP:n puolella.

PHP:ssä käytetään UNIX-aikaleimoja, jotka ovat aina UTC-ajassa. Aikaleiman voi muuntaa MySQL:ssä DATETIME-muotoon funktiolla FROM_UNIXTIME. PHP:ssä muunnos MySQL:n ymmärtämään tekstimuotoon onnistuu date-funktiolla (formaatti "Y-m-d H:i:s"), mutta tällöin on tärkeää, että PHP:n ja MySQL:n aikavyöhykeasetukset ovat samat.

MySQL palauttaa DATETIME-kentän arvon tekstimuodossa valitun aikavyöhykkeen mukaan. Arvon voi muuntaa MySQL:ssä UNIX-aikaleimaksi funktiolla UNIX_TIMESTAMP. PHP:n puolella muunnos onnistuu funktiolla strtotime, mutta tällöin on taas tärkeää, että PHP:n ja MySQL:n aikavyöhykeasetukset ovat samat.

MySQL:n DATETIME-kenttä sisältää UTC- tai GMT-aikaleiman. Kun aikoja tallennetaan ja haetaan, MySQL tekee niille automaattisesti muunnoksen oman aikavyöhykeasetuksensa mukaan.

Tarkoitiko jotain tälläistä?

echo date('d.m.Y H:i',strtotime($rivi["DateTime"]));

Metabolix: ja miksei muka MySQL:n omia konversiofunktioita saisi käyttää? Ei tarvitse kikkailla sen jälkeen enää yhtään mitään.

SELECT DATE_FORMAT(<date>, '%d.%m.%Y') as d FROM <table>

<table> = Taulu, johon kysely kohdistetaan.
<date> = Taulun sarake, josta luetaan date- tai datetime-tyyppinen arvo.

Tiedot tietokantaan voi tallentaa sitten seuraavasti, kun taulun sarakkeen tietotyyppinä on date (tai datetime):

  I: INSERT INTO <table> (pvm) VALUES ( FROM_UNIXTIME(<unixtime>) )
 II: INSERT INTO <table> (pvm) VALUES ('2010-01-23')
III: INSERT INTO <table> (pvm) VALUES( NOW() )

Tapa (I) on käyttökelpoinen, jos PHP:n puolella on käytössä timestamppi unix timenä eli kokonaislukuna.

Tapa (II) demonstroi, että MySQL osaa dekoodata merkkijonosta päivämäärän ongelmitta. En tiedä tahi jaksa ottaa selvää, että missä kaikissa muodoissa pvm:t hyväksytään, mutta YYYY-MM-DD on jonkin sortin standardi muoto, jota olen itse käyttänyt.

Tavan (III) mukaisesti päiväyksen saa myös ihan suoraan MySQL:n sisäisesti NOW()-funktiolla.

The Alchemist: Pahoittelen kammottavaa epätarkkuutta. Tarkoitukseni oli korostaa, että sivulle tulostettavalla muodolla ei ole mitään tekemistä tietokannan sisällön kanssa, vaan aikaleima tallennetaan tietokantaan samalla tavalla aikavyöhykkeestä ja lopullisesta esitystavasta riippumatta.

Toki tuloste on mahdollista muotoilla jo SQL-kyselyssä. Kuitenkin monissa isoissa sovelluksissa aikoja täytyy käsitellä tavalla tai toisella myös PHP:n puolella, joten ei ole käytännöllistä palauttaa tietokantatasolta aikaa muotoiltuna. Lisäksi tuntuisi riskialttiilta, että ohjelmiston lokalisointi vaikuttaisi tietokantakyselyihin.

The Alchemist kirjoitti:

Tapa (II) demonstroi, että MySQL osaa dekoodata merkkijonosta päivämäärän ongelmitta. En tiedä tahi jaksa ottaa selvää, että missä kaikissa muodoissa pvm:t hyväksytään, mutta YYYY-MM-DD on jonkin sortin standardi muoto, jota olen itse käyttänyt.

MySQL:n käyttämä muoto on nimenomaan se, jonka PHP:stä saa ulos date-funktion formaatilla "Y-m-d H:i:s". Muut kelvolliset vaihtoehdot voi tarkistaa manuaalista; osien järjestys on kaikissa sama. Mutta kuten jos aiemmin sanoin, tuossa tapauksessa pitää joko tehdä tarpeeksi yksinkertaista, yhdellä aikavyöhykkeellä toimivaa ohjelmaa tai huolehtia, että MySQL ja PHP ovat samaa mieltä aikavyöhykkeestä, jottei tule ikäviä yllätyksiä.