Sorteren op datum uit database

[Maverick2001]

Voor een overzicht van evenementen haal ik de info uit mijn database.

Het is de bedoeling dat de pagina ze sorteerd op datum. Omdat de standaard invoer in de database bij een 'date' veld is 'yyyy-mm-dd' heb ik in mijn my sql statement het volgende gezet:

PHP:

$xx=mysql_query("SELECT *, DATE_FORMAT(edatum, '%d-%m-%Y') AS edatum FROM agenda ORDER BY edatum ASC");

Ik wil namelijk dat hij gewoon de nederlandse output van een datum doet 'dd-mm-yyyy'.

Nu is het probleem dat de output wel gesorteerd wordt, echter nu op de dag en niet meer op dag/maand/jaar (begrijp dat dat logisch klinkt als je het format in de mysql aanpast). Hoe krijg ik het nu wel voorelkaar dat hij netjes op de volledige datum blijft sorteren.

[Reinier]

Geef de geformatteerde datum een andere alias en sorteer op edatum, zou goed moeten gaan

[zwippie]

Dan kun je beter geen date-formatting in je query doen, maar dit pas bij het outputten in php doen. Daarvoor heb je in php bijvoorbeeld strftime en date.

[ Voor 13% gewijzigd door zwippie op 05-02-2007 10:15 ]

[Verwijderd]

Vaak is het makkelijker om gebruik te maken van Unix timestamps ipv het data formaat van MySQL, niet in de laatste plaats omdat de werking van dat veld nogal eens wordt aangepast.

Tenzij je specifieke functies van MySQL nodig hebt op dat veld die niet heel gemakkelijk in PHP te doen zijn (denk aan het automatisch bijwerken bij een update, iets wat in PHP niet geheel te ondervangen is) dan zou ik overstappen op timestamps en de date functie van PHP gebruiken.

Wat betreft je vraag: Gewoon het datum veld als een andere naam opvragen (bijvoorbeeld as edatum_form) dan zou je probleem opgelost moeten zijn (en dan dus wel blijven sorteren op edatum)

[Maverick2001]

Heren bedankt! De oplossing van Reinier is het makkelijkste. De overige oplossingen zijn zeker interessant en ga me er ook in verdiepen. De toepassing zoals hij nu gebruikt wordt is niet zo uitgebreid dus de oplossing hoeft ook niet te uitgebreid te zijn. Voor het geval dat andere mensen ook met dit probleem zitten post ik de nieuwe werkende code:

PHP:

 $xx=mysql_query("SELECT *, DATE_FORMAT(edatum, '%d-%m-%Y') AS ddatum FROM agenda ORDER BY edatum ASC")

In de array heb ik:

PHP:

$edatum = $row["ddatum"];

[Maverick2001]

Ik dacht dat het werkt maar het gaat nog niet goed.

De output is wel goed maar ik vergelijk de datum in de database met de huidige datum

PHP:

if ($edatum >= $datumvandaag)

Nu is het de bedoeling dat hij op de volledige datum kijkt of die groter of gelijk is aan de huidige datum.
De output is echter dat ie alleen kijkt naar de dag. Hiermee krijg ik 10-02-2007 wel te zien maar bijvoorbeeld 06-04-2010 niet.

[Robtimus]

Hoe heb je $datumvandaag een waarde gegeven? Als string? Dan gaat hij lexicografisch vergelijken, en dan komt 06xxxx altijd voor 10xxxx.

[Maverick2001]

Ik heb:

$datumvandaag = date('d-m-Y');

om de datum te formateren.

[Robtimus]

Maverick2001 schreef op zaterdag 10 februari 2007 @ 22:02:
Ik heb:

$datumvandaag = date('d-m-Y');

om de datum te formateren.

En daar zit dus het probleem.

Aangezien je op hele dagen wilt controleren kun je niet de timestamp van "nu" nemen, iig niet zonder aanpassingen.

In VB, ASP etc zou DateDiff hier heel handig zijn (DateDiff(d, $datumvandaag, $edatum) >= 0), maar zoiets ken ik niet in PHP.

Ik zie zo 2 oplossingen:

• Roep strtotime aan met als parameter het resultaat van date('d-m-Y'). Je krijgt hierdoor de timestamp van dezelfde dag maar dan middernacht. Hiermee kan je dan wel timestamps direct vergelijken.
• Haal zowel ddatum uit de database als $datumvandaag op met Y-m-d, mss beter nog forceren dat dagen en maanden 2 cijfers zijn door YYYY-mm-dd te gebruiken. Als je deze 2 strings dan lexicografisch gaat vergelijken lukt dit wel omdat jaar voor maand gaat, en maand voor dag.

[Verwijderd]

Of zoals al eerder gezegd:

Compleet overstappen op Unix timestamps in de database.

Het is altijd goed om de data en de representatie van de data gescheiden te houden, wat je nu in feite aan het doen bent is een opgemaakte string (de datum) op te slaan in de database. Je slaat dus enkel de representatie van de data op.

Nu is de datum natuurlijk een heel lastig verhaal gezien wij dat alleen maar kennen als zijnde representatieve strings. Veel databases hebben hun eigen time/date formaat, maar dat is een beetje het opnieuw uitvinden van het wiel.

Zo heb je bijvoorbeeld ISO-8601 wat een goede manier om de datum op te slaan is, en je hebt ook RFC 2822 waarin ook de data zit. Maar tot op zekere hoogte zijn dit erg lastige formaten om mee te werken gezien je voor het rekenen ermee gelijk weer allerlei ingewikkelde functies nodig hebt (zoals de genoemde datediff).

Om dit probleem op te lossen bestaat al sinds 1972 zogenaamde POSIX tijd of Unix tijd oftewel de Unix timestamp. Dit is simpelweg het aantal seconden dat er voorbij gegaan is sinds 1970-01-01T00:00:00Z.

Dit is natuurlijk erg eenvoudig om op te slaan (het is gewoon een integer, of als je heel precies wilt zijn een float), eenvoudig om mee te rekenen (optellen, aftrekken, allemaal geen probleem). Ook hoef je verder niet na te denken over schrikkeljaren, schrikkelsecondes etc want dat zijn ook gewoon seconden die verlopen in de timestamp.

Wat je wel nodig hebt zijn functies om deze timestamp weer om te zetten naar een leesbaar formaat, gelukkig is daar een goede library van beschikbaar en daardoor is die functie beschikbaar voor bijna ieder systeem. In PHP is dit de functie date.

Ook heb je een manier nodig om te rekenen in logische eenheden zoals bijvoorbeeld een dag. Normaal gesproken is een dag 86400 seconden, maar soms ook 86401 of 86399, zo ook met weken, maanden, jaren etc. Ook daarin voorziet de library, beschikbaar in PHP onder strtotime waarmee het mogelijk is een waarde in te geven als +3 weeks en daarmee gemakkelijk te rekenen.

Daarnaast hoef je geen rekening te houden met tijdzones, ook hierin voorziet de timestamp gezien het aantal secondes sinds 01-01-1970 00:00 Zulu (GMT dus).

Al met al ruim de moeite waard om over te stappen op de Unix timestamp dus, het scheelt een hoop moeite. Waarom database fabrikanten (en anderen) het altijd weer nodig vinden een eigen formaat uit te vinden heb ik nooit begrepen...

[Creepy]

Zolang je de datum vegelijking direct in MySQL blijft doen en de datum niet omzet naar een andere representatie dan is er niks aan de hand. Als je timestamps gaat gebruiken en in de query je datum omzet naar een andere representatie en daar vergelijkingen mee gaat maken dan gaat het ook mis.

Zorg gewoon dat je altijd yyyy-mm-dd gebruikt in je queries en vergelijking en zet deze representatie alleen om als deze naar het scherm afgedrukt moet worden. Daarnaast, als je alleen een datum wilt opslaand, gebruik dan ook gewoon het type wat daar het best bij past: date. Bijv een geboortedatum opslaan in een timestap veld gaat je meer problemen opleven dan dat je nu hebt

Een timestamp aanraden vind ik dan ook een beetje loos. Er zijn prima data functies om met de MySQL dataum formaat te werken en die zijn echt niet moeilijk.

[Verwijderd]

Creepy schreef op zondag 11 februari 2007 @ 11:21:
Een timestamp aanraden vind ik dan ook een beetje loos. Er zijn prima data functies om met de MySQL dataum formaat te werken en die zijn echt niet moeilijk.

Daar ben ik het mee eens. Je hoort gegevens gewoon op te slaan in het meest geschikte native formaat. De UNIX_TIMESTAMP functie is er bij MySQL om bij het ophalen van de gegevens een handig formaat te hebben om buiten MySQL mee verder te werken.

[Verwijderd]

Creepy schreef op zondag 11 februari 2007 @ 11:21:
Bijv een geboortedatum opslaan in een timestap veld gaat je meer problemen opleven dan dat je nu hebt

Wat een onzin, sinds versie 5.1.0 kan een timestamp in PHP tussen Fri, 13 Dec 1901 20:45:54 GMT en Tue, 19 Jan 2038 03:14:07 GMT zitten. Daarvoor ging het onder alle systemen behalve Windows ook al prima.

Een timestamp aanraden vind ik dan ook een beetje loos. Er zijn prima data functies om met de MySQL dataum formaat te werken en die zijn echt niet moeilijk.

Meerdere functies in MySQL tov 2 universele functies in PHP, daarnaast heeft MySQL ook genoeg functies om met een timestamp om te gaan. Zeker voor beginners zijn timestamps veel gemakkelijker om mee te werken.

Een timestamp is in ieder geval altijd een timestamp, naar wat voor systeem je het ook brengt, waar ook ter wereld met wat voor kalender dan ook. MySQL heeft nog wel eens de neiging om het formaat van het datum veld te wijzigen van versie tot versie en ook hangt het soms nog eens af van de internationale instellingen van de server hoe het erin en eruit gaat.

Er zijn systemen waarbij het interne datum formaat weinig tot geen nadelen oplevert en misschien zelfs voordelen geeft, maar bij de combo PHP en MySQL is het gebruik van de timestamp zeer zeker aan te raden.

Dat is niet loos, daar zijn een hoop redenen voor....

Wat wel loos is is het argument "scheiden van data en representatie van data" te counteren met het argument "zonder werkt het ook goed".

[ Voor 4% gewijzigd door Verwijderd op 11-02-2007 11:52 ]

[Creepy]

Eeh.. ik zeg juist ook dat ie data en represenatie van data moet scheiden. Ik zeg alleen dat het gebruikte datatype in MySQL daarbij niet veel uitmaakt.

Verwijderd schreef op zondag 11 februari 2007 @ 11:50:
[...]
Wat een onzin, sinds versie 5.1.0 kan een timestamp in PHP tussen Fri, 13 Dec 1901 20:45:54 GMT en Tue, 19 Jan 2038 03:14:07 GMT zitten. Daarvoor ging het onder alle systemen behalve Windows ook al prima.

Dus het gebruik van een timestamp is ook afhankelijk van de MySQL versie net als een date type.

[ Voor 59% gewijzigd door Creepy op 11-02-2007 16:19 ]

[Verwijderd]

Creepy schreef op zondag 11 februari 2007 @ 16:16:
Dus het gebruik van een timestamp is ook afhankelijk van de MySQL versie net als een date type.

Nee afhankelijk van de PHP versie, MySQL kan er volgens mij ook al sinds een oude versie mee om gaan.

Daarnaast hoeft MySQL er niets mee te doen, alle berekeningen kun je van te voren doen (bijvoorbeeld +1 week) en vervolgens MySQL het lekker als integer laten behandelen (wat het in feite is)

[ Voor 23% gewijzigd door Verwijderd op 11-02-2007 17:39 ]

[Janoz]

en vervolgens MySQL het lekker als integer laten behandelen (wat het in feite is)

In feite? Waarom werk je dan niet gewoon met bitmasks? Op de computer is dat uiteindelijk alles.

Een datum is een datum. Het is geen integer of een string. Je kunt beiden gebruiken om een datum te representeren, maar dat betekent niet dat een datum dus een integer is.

Dat php enkel de datatypes String en Number kent betekend nog niet dat dat dan ook de enige types zijn.

[Verwijderd]

Of je 't aan de client (datum naar timestamp conversie) of de database (eigen datetime veld, en handige bijbehorende functies) overlaat maakt m.i. geen bal uit. Timestamps zijn niet heilig.

[Verwijderd]

Janoz schreef op zondag 11 februari 2007 @ 17:54:
[...]


In feite? Waarom werk je dan niet gewoon met bitmasks? Op de computer is dat uiteindelijk alles.

Een datum is een datum. Het is geen integer of een string. Je kunt beiden gebruiken om een datum te representeren, maar dat betekent niet dat een datum dus een integer is.

Dat php enkel de datatypes String en Number kent betekend nog niet dat dat dan ook de enige types zijn.

Nogal makkelijk om iemands woorden te verdraaien om zo proberen je gelijk te krijgen he?

Een timestamp is in feite een integer (de datum en tijd opgeslagen in een integer) en je kan daar inderdaad ook bit-masks op toepassen mocht je dat willen. Dat wil dus echt niet zeggen dat de datum in feite een integer is en dat is ook echt niet wat ik probeer te zeggen.

Ook is PHP echt niet de enige taal waarin timestamps veel gebruikt worden, ze kunnen (en worden vaak ook) in heel veel talen gebruikt worden. Ook kent PHP echt wel meer datatypes, dat PHP nu weer 'handige' type-juggling doet is een heel ander verhaal.

PHP werd alleen genoemd omdat de TS daarmee bezig was, hij heeft er niet zoveel aan om een voorbeeld in C te krijgen als hij met PHP werkt.

Het punt is: Voor de TS en programmeurs met weinig ervaring die gebruik maken van PHP en MySQL kunnen het beste kiezen voor een unix timestamp en dit om de volgende redenen:

- Er zijn maar 2 functies in PHP die je hiervoor hoeft te kennen, namelijk date en strtotime om zo goed als alles wat je maar wil met tijd en datum te doen

- MySQL kan prima omgaan met timestamps, PHP kan minder goed omgaan met MySQL's data type (je gaat dan vaak toch weer converteren naar een timestamp omdat PHP daar goed mee werkt)

- MySQL het eigen datatype nogal eens van versie op versie en afhankelijk van de instellingen wil wijzigen wat voor problemen kan zorgen

- Timestamps erg goed uitwisselbaar zijn naar andere systemen (en al hetzelfde sinds 1972)

Dat zijn de redenen

Dat verder het met MySQL ook prima voor elkaar te krijgen is en timestamps niet heilig zijn doet niets af aan deze redenen. Een goede tip voor de TS (wat z'n problemen vrijwel zeker zal oplossen) is dus om over te stappen op timestamps.

Maar voel je vrij om de nadelen van timestamps op te noemen, dan kan hij een eigen beslissing maken.

[Janoz]

Zolang je bij het uitlezen de date velden via de mysql functies omzet naar timestamps zodat php er ook wat mee kan, en je bij het inserten ze weer omzet naar date hoef je maar 2 conversie fucnties te onthouden en kun je in php met de (voor php) normale manier werken en kun je in mysql ook gebruik maken van alle beschikbare date time functies. Juist programmeurs met weinig ervaring zou ik die conversie functies aanraden.

Kijk enkel eens welke mogelijkheden de date functies van mysql hebben. Wanneer je dingen op dezelfde dag, dezelfde week of dezelfde maand wilt selecteren bijvoorbeeld. Met timestamps is dat behoorlijk lastig. Denk alleen al aan zomer en wintertijd. Ideale mogelijkheid om vervelende en lastig achter te komen bugs in je applicatie te introduceren.

[Verwijderd]

Verwijderd schreef op zondag 11 februari 2007 @ 18:24:
Maar voel je vrij om de nadelen van timestamps op te noemen, dan kan hij een eigen beslissing maken.

- 1902 - 2037 (ongeveer). Datums die daar buiten vallen passen niet in een timestamp.
- Een granularity van 1 seconde. Voor veel toepassingen is dat niet precies genoeg.
- Datetime velden bestaan niet voor niks. Ik heb weinig ervaring met MySQL, maar in bv. MSSQL of InterBase/Firebird kan ik in stored procs of triggers veel en veel meer met datetime velden en functies dan met een integer die doet alsof 'ie een datum/tijd representatie is...