Keskustelu: Nettisivujen teko: Ongelmia Mysql:n timestampin ja php date -funktion kanssa

Tervehdys
Tein pienen uutisskriptin php+mysql -yhdistelmällä. Uutisten listauksen ideana on laittaa viimeksi muokattu ylimmäksi jne. Uutisen päiväyksen toteutun mysql:n timestampilla, jonka avulla edellä mainittu järjestäminen tapahtuu helposti. Ongelma kuitenkin on, että en onnistu muuntamaan sitä luettavaan muotoon tulostusta varten.

Timestampin määrittelin "uutiset" -tauluun seuraavasti:

uutiset_paiva timestamp (14) not null

Tällaisella pätkällä yritän muunta tuon luettavaan muotoon (tuo $uutiset_paiva on tietysti määritelty queryn yhteydessä):

$paivays = date("j.n.Y", $uutiset_paiva);

Jos yritän tulostaa timestampin ($uutiset_paiva) suoraan ilman muunnosta lopputulos on oikein, mutta tuon muuntamisen tuloksena päiväyksestä ($paivays) tulee 19.1.2038 (eli tuon unixajan loppumispäivä)

Kokeilin piruuttani tuota date funktiota niin, että kirjoitin unixajan siihen suoraan...

$paivays = date("j.n.Y", "20040618131245");

...ja avot: lopputulos oli sama 19.1.2038.

Ympäristö: windows 2000, apache 1.3.29, php 4.3.3 ja mysql 4.0.18.

Apua

Antti

http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html

Column Type       Display Format
TIMESTAMP(14)     YYYYMMDDHHMMSS
TIMESTAMP(12)     YYMMDDHHMMSS
TIMESTAMP(10)     YYMMDDHHMM
TIMESTAMP(8)      YYYYMMDD
TIMESTAMP(6)      YYMMDD
TIMESTAMP(4)      YYMM
TIMESTAMP(2)      YY

Oli itsellekkin uusi juttu. Oon tallentanut aina aikaleiman mysliin php:n omalla time()-funkkarilla.

a) kannattaa suosia ajan tallentamisessa DATETIME-tyyppiä

http://dev.mysql.com/doc/mysql/en/Date_and_time_type_overview.html

lainaus:

From MySQL 4.1 on, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'.

b) turha lähteä purkkapuolelle kun kanta konvertoi kiltisti muotoon kuin muotoon

SELECT otsikko, uutinen, DATE_FORMAT(aikaleima, '%d.%m.%Y %H:%i:%s') AS aika FROM taulu ORDER BY aikaileima DESC

AS on tärkeä muistaa koska muuten aikaleimalla järjestäminen järjestääkin muotoon

1.6.2004 13:33:33
1.9.2003 6:16:16
3.3.2004 14:15:16
...

EDIT: hipsut unohtui lainauksesta... hiivatin lainaustagit, suostu toimimaan

Okei tämä ehkä kuulosta guruista vähän tyhmältä, mutta en nyt ihan ymmärtänyt tuota leftoverin esimerkkiä.

Eli tuon "aikaleima" nimisen muuttujan hakeminen:

DATE_FORMAT(aikaleima, '%d.%m%Y %H:%i:&s') AS aika

Eli mikä on tuo "aika" -juttu ? DATE_FORMAT ilmeisesti muodostaa "aikaleiman" perässä tulevan stringin mukaisesti ja AS määrittelee minkä mukaisesti järjestetään. Suomea tuskin mysql ymmärtää, joten mikä on tuo "aika" tuossa lauseessa?

Toisena hommana kysyisin vielä, että toimiikohan tuo mysql 4.0.18 versiossa? Tuossa kun mainitaan 4.1 versio

Kiitoksia joka tapauksessa kummallekin vastaajalle!

Tossa esimerkissa aikaleima sarakkeessa oleva päivämäärä muotoillaan tohon muotoon (päivä, kuukausi, vuosi. tunnit, minuutit, sekunnit), ja as tekee siitä uuden sarakkeen nimeltään aika jossa on muotoiltu päivämäärä. as määrettä ei ole pakko käyttää, mutta selvyyden/helppouden vuoksi sitä kannattaa käyttää. as jälkeen tuleva on vain sarakkeen nimi, joten se voi olla mitäkieltä tahansa, tosin selvyyden vuoksi se kannattaa nimetä aina järkevästi.

Opiskelija tuossa jo kommentoikin, itse pidän AS-määritteestä koska otan arvot php:n puolella mysql_fetch_associlla. Esimerkit:

<?php
// haku
$row = mysql_fetch_assoc($result);

// Ilman AS-määritettä
echo $row['DATE_FORMAT(aikaleima, \'%d.%m.%Y %H:%i:%s\')'];

// As-määritteen kanssa
echo $row['aika'];
?>

Eli helpottaa melko mukavasti tulostelua... Lisäksi irrotin ehkä hieman huonosti tuon lauseen asiayhteydestä. DATE_FORMAT on toiminut MySQL:ssä jo iät ja ajat, mutta MySQL:n versiosta 4.1 lähtien TIMESTAMP-kenttä palauttaa aina oletuksena 'YYYY-MM-DD HH:MM:SS' eli samassa muodossa kuin DATETIME, tämän takia suosittelen DATETIME-kenttää.

Optimoinnin kannalta kenttä ei kuitenkaan ole paras mahdollinen (vie 3 tavua enemmän kuin TIMESTAMP), mutta voi ainakin olla varma että skripti toimii varmasti myös vuonna 9999 kun TIMESTAMP hyytyy jo vuoteen 2038 :)

EDIT: typoja...

EDITIN EDITTI: http://www.futureality.net/oputka.png ei näköjään tykkää väärin escapetetuista hipsuista...

Itse käytän aikaratkaisuissa seuraavanlaista metodia:

Luon aikaleiman unixaikana eli sekunteina [time()].

Tämän jälkeen tallennan sen tietokantaan lukuarvona VARCHAR muotoiseen kenttään, koska se on muuttuvan mittainen arvo (SQL perusteet).

En käytä tietokantaa mihinkään muuhun kuin tiedon tallentamiseen, hakuun ja lukuun. Pyrin välttämään kyselyjonoissa ylimääräisten funktioiden käyttöä jos pystyn sen hoitamaan nopeammin esim. PHP:lla. Näyttäisi siltä, että PHP pystyy käsittelemään nopeammin nuo aikajutut eli aikaleiman muutan takaisin luettavaan muotoon käyttämällä getdate() -funktiolla. Siitä saa sitten haluamassaan muodossa huomattavasti helpommin nuo kaikki arvot tarvittavassa muodossa.

Ei tarvitse myöskään miettiä niitä ongelmia jos tietokanta alusta vaihtuu tai tietokannan omissa aikafunktioissa tapahtuu muutoksia.

Hieman kyllä epäilen Wizardin väitteitä. Oletko testannut? Jos olet niin oletko testannut seuraavasti:

1. testi
- PHP: Ajan luonti time
- MySQL: Datan vienti
- MySQL: Datan nouto
- PHP: Ajan muotoilu getdate
- PHP: Ajan tulostus echo

2. testi
- MySQL: Datan vienti
- MySQL: Ajan muotoilu noudon yhteydessä
- PHP: Ajan tulostus echo

Veikkaan että 2. testi kyykyttää 1. testin jo pelkästään kahden PHP-puolella pois jäävän funktion vuoksi. Aina se tietokanta käsittelee nopeammin omaa dataa kuin ulkopuolinen tulkki sille noudettua dataa. Luonnollisesti DATE_FORMAT hidastaa kyselyä joillakin mikrosekunneilla, mutta uskoisin että time() ja getdate() hidastaa itse skriptiä jo useilla mikrosekunneilla.

Oma puhti ei riittänyt testailemiseen...

EDIT: VARCHAR ei kuulosta loogiselta ratkaisulta. Jos (kun) kyseessä on kokonaisluku, toimisi INT UNSIGNED paremmin (en ole varma riittääkö INT mutta löytyyhän sitä suurempiakin kokonaislukukenttiä). IMO VARCHARria ei tulisi tukea kuin UNIQUE-kentissä.

Testasin tätä eräässä palvelussani jossa tietokannat ja web koneet ovat erillisiä. Testi ulottui lähinnä vain käyttäjien käytön tilastointiin.

Testit kyllä osoittivat sen minkä takia itse siirryinkin tuota metodia käyttämään. SQL tietokantapalvelimelle kertyi koko ajan hirvittävästi kuormaa ja sen prosessorin käyttöaste oli 300 sekunnin sykleissä mitattuna 87%. Palvelun luonne kasaa nimenomaan tietokantaan vaatimuksia.

Scripteissä mitattiin aikoja omilla ajastamilla.

Ajastin lähti käyntiin kun sivua pyydettiin. Se mittasi erikseen SQL kyselyihin menevät ajat sekä katkaisi ajastimen lopullisesti kun koko tieto oli noudettu ja käsitelty siihen muotoon, että voitiin alkaa tulostamaan itse HTML -sivua.

Ajastimen katkaisun jälkeen tieto heitettiin tietokantaan jatkotilastointia varten.

Erot ovat pieniä yksittäin, mutta kokonaisajassa se näkyy. Web palvelimilla oli käytössään enemmän suoritinaikaa sekä tehoja kuin tietokantapalvelimilla. Näin ollen kuormansiirto tietokantapalvelimelta web palvelimelle sai aikaan palvelun nopeutumista.

Harvoin vähänkään isommissa palveluissa localhostissa toimii sekä web palvelin että tietokantapalvelin. Kyseisessä palvelussa on käytössä 2.8GHz PIV koneita joissa on muistia riittävästi (n. 2GB). Muukin suorituskyky kyllä kestää tutkintaa.

Mitä tulee kentän valintaan, niin se on sellainen, että tarvittaessa koko alusta voidaan heittää Oraclen tai MSSQL serverin päälle puuttumatta ollenkaan tietuemuotoihin. Tosin tällä hetkellä MySQL tarjoaa meille sellaisen skaalautuvuuden, että ei ole järkeä vaihtaa sitä mihinkään.

Tietysti jos löytyy riittävästi ruutia tietokantapalvelimista, niin tilanne saattaa kääntyä toisinpäin. Meillä statistiikka osoittaa jotain ja me teemme silloin niin, että se kääntyy palvelulle edulliseksi.

Tämän takia järjestelmämme painopiste on mennyt siihen, että mitä vähemmän tuotetaan kuormaa tietokantapalvelimelle, niin sen parempi. Aikojen käsittely on näistä ehkä se suurin. Muutenkin olemme poistaneet kaikki lajittelut tietokannan harteilta jos kysely palauttaa saman lopputuloksen lajittelusta huolimatta ja olemme siirtäneet lajittelut PHP:n harteille. Se lisäsi myös suorituskykyä jonkin verran.

Ei noilla testeillä voi kuin uskoa. Aika varma elementti tuloksessa on ollut web-palvelimen tehokkuus, mutta siltikin vaikea myöntää että enemmän olisi tehokkaampaa. Pakko tuo on testata parissa eri ympäristössä että näkee käyttäytymisen myös pienemmissä järjestelmissä ja sen mukaan tietää optimoida.

Emme itsekään käytä leipätyön projektissa tietokantapuolen datan formatointia, vaan suoritame sen palvelinpuolella. Syyt tähän tosin eivät ole optimointi vaan tulevien käyttäjien maantieteelliset sijainnit.

Koskaan ei näköjään ole "guppa" oppiakseen uutta :D