[SQL] Sortering op datum, zonder jaartal (Unix Timestamp)

[Verwijderd]

Op het moment ben ik bezig een verjaardagskalender te maken met PHP/SQL. De bedoeling is dat je uiteindelijk in een interface mensen kunt toevoegen met naam en geboortedatum, waarna op de hoofdpagina van de site de verjaardagen in de komende pakweg 14 dagen worden getoond. Dit moet dan gebeuren in het format "26 aug - Jan Klaasen (1960)".

Enfin, nu heb ik een SQL tabel "kalender" met daarin drie kolommen: "id", "name" en "bday", waarbij de laatste altijd gevuld wordt door een Unix Timestamp. Om de ingevoerde verjaardagen uit te lezen gebruik ik op het moment de volgende query:

$sql_query=mysql_query("SELECT * FROM kalender ORDER BY bday");

Hier heb ik nog niet in verwerkt dat enkel de verjaardagen in de komende 14 dagen moeten worden getoond, maar dat doet er nu ook nog even niet toe. Punt is dat ik nu wel een lijst krijg met alle ingevoerde verjaardagen, gesorteerd op datum, maar dat betekent dat allereerst wordt gesorteerd op jaar en dat is niet de bedoeling. Ik wil dat er wordt gesorteerd op de maand en de dag in de timestamp, anders komen bovenaan de mensen te staan die het oudste zijn i.p.v. de mensen die het eerste jarig zijn.

Ik heb geprobeerd om de date() functie te gebruiken in de SQL query zodat ik 'bday' kan omzetten naar date('d',bday). Zoals je wel kunt raden had dit geen succes: "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource"

Nu zou ik kunnen begrijpen dat een timestamp converteren in een SQL query problematisch is omdat de query slechts is gericht op het ophalen van data uit de database, maar er moet toch wel een manier zijn om de Unix Timestamp te kunnen gebruiken zonder meteen te sorteren op jaar. Mocht dat niet zo zijn kan ik nog wel andere manieren verzinnen waarbij ik (deels) af stap van het gebruik van de timestamp, maar eerst wil ik graag proberen dit zo op te lossen.

Hopelijk kan iemand me hiermee helpen.

[RobIII]

quote: http://dev.mysql.com/doc/...e-and-time-functions.html

DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
-> '2003-12-31'

Dat gaat niet werken nee
Je verwart SQL statments met PHP:

quote: http://www.php.net/manual/en/function.date.php

date
(PHP 3, PHP 4, PHP 5)
date -- Format a local time/date

Description
string date ( string format [, int timestamp] )

Returns a string formatted according to the given format string using the given integer timestamp or the current local time if no timestamp is given. In other words, timestamp is optional and defaults to the value of time().
<snip>

SQL:

SELECT * FROM kalender order by month(bday), day(bDay)

Zoiets werkt wel

[Raynman]

RobIII schreef op zaterdag 26 augustus 2006 @ 06:23:
Zoiets werkt wel

Dat lijkt me niet, want hij praat over een unix timestamp en met die date() functie bedoelt hij dan vast PHP's date().

Op deze manier wordt het erg moeilijk. Er wordt ook niet voor niets gezegd dat je altijd een datumtype (van je database server) moet kiezen voor het opslaan van datums. Als je gewoon het type DATE kiest, kun je de query van RobIII gebruiken.

[seamus21]

Als je eenmaal timestamps uit de db hebt gehaald kun je de maand er op deze manier uithalen, ervan uitgaande dat je de timestamps er met bijvoorbeeld mktime() instopt.

PHP:

$maand = date("m", $timestamp); // m = maand met leading zeros

Voor jaar en dagen hetzelfde. Op php.net staat een uitgebreide omschrijving van de param lijst van date().

[ Voor 43% gewijzigd door seamus21 op 26-08-2006 11:12 ]

[Raynman]

seamus21 schreef op zaterdag 26 augustus 2006 @ 10:56:
Als je eenmaal timestamps uit de db hebt gehaald kun je de maand er op deze manier uithalen, ervan uitgaande dat je de timestamps er met bijvoorbeeld mktime() instopt.

Maar dan moet je dus altijd alle verjaardagen uit de database halen om ze vervolgens naar een bruikbaar formaat om te zetten, waarna je kunt gaan sorteren en alleen de komende veertien dagen tonen. Dat is niet echt efficiënt.

[seamus21]

numlockrondje schreef op zaterdag 26 augustus 2006 @ 11:09:
[...]
Maar dan moet je dus altijd alle verjaardagen uit de database halen om ze vervolgens naar een bruikbaar formaat om te zetten, waarna je kunt gaan sorteren en alleen de komende veertien dagen tonen. Dat is niet echt efficiënt.

Ik zei ook niet dat het efficiënt was. Ik vertel alleen hoe hij de maand uit zijn huidige timestamp kan halen.

[bakkerl]

Je kan je querie omzetten naar

$sql_query=mysql_query("SELECT *, FROM_UNIXTIME(bday, '%c') as bdmonth, FROM_UNIXTIME(bdday, '%e') as bdday FROM kalender ORDER BY bdmonth, bdday");

Dan heb je uit de unixtime stamp de maand (bdmonth) en dag (bdday) gehaald en daarop gesorteerd.
Eventueel kun bij de help van FROM_UNIXTIME kijken wat nog meer mogelijkheden zijn.

De querie is ongetest en zo ingetikt.

[Verwijderd]

Enfin, nu heb ik een SQL tabel "kalender" met daarin drie kolommen: "id", "name" en "bday", waarbij de laatste altijd gevuld wordt door een Unix Timestamp.

Wanneer die tabel puur als verjaardagskalender gebruikt wordt, is 't dan niet handiger om die "bday" timestamp op te splitsen in "birthday" char(4) en "year_of_birth" int?
In "birthday" sla je dan in 'mmdd' formaat de verjaardag op, en daar is veel gemakkelijker op te zoeken en te sorteren.

[Grijze Vos]

Gebruik gewoon een DATE veld ipv een Unix Timestamp, maakt het een stuk makkelijker (en zinniger.)

[Verwijderd]

Voor een verjaardagskalender is een DATE field net zo (on)zinnig als een timestamp.
In beide gevallen zit er nl. ook nog jaar-informatie in, en die heb je niet nodig wanneer alleen dag en maand (de verjaardag) van belang zijn.

[RobIII]

Verwijderd schreef op zondag 27 augustus 2006 @ 01:55:
Voor een verjaardagskalender is een DATE field net zo (on)zinnig als een timestamp.
In beide gevallen zit er nl. ook nog jaar-informatie in, en die heb je niet nodig wanneer alleen dag en maand (de verjaardag) van belang zijn.

Een jaartal is wel handig als je meteen wil weten hoe oud diegene wordt op z'n verjaardag

[FragFrog]

bakkerl schreef op zaterdag 26 augustus 2006 @ 11:19:
Je kan je querie omzetten naar

$sql_query=mysql_query("SELECT *, FROM_UNIXTIME(bday, '%c') as bdmonth, FROM_UNIXTIME(bdday, '%e') as bdday FROM kalender ORDER BY bdmonth, bdday");

Dan heb je uit de unixtime stamp de maand (bdmonth) en dag (bdday) gehaald en daarop gesorteerd.
Eventueel kun bij de help van FROM_UNIXTIME kijken wat nog meer mogelijkheden zijn.

De querie is ongetest en zo ingetikt.

What he said

Als je't dan vervolgens zo doet krijg je netjes de data die je wilt:

code:

SELECT     *, FROM_UNIXTIME(bday) as bddate
FROM       kalender
WHERE      bddate IN (NOW(), NOW() + INTERVAL 2 WEEKS)
ORDER BY   DAYOFYEAR(bddate)

_{Met dezelfde disclaimer}

Ben het er overigens wel mee eens dat een date(time) veld logischer is dan een timestamp. MySQL kan echt opmerkelijk goed met datums (data? ) omgaan, in het juiste format eruit gooien etc is geen enkel probleem en hoef je echt niet PHP's mktime voor te gebruiken

//edit
Mmm, bedenk me net dat dit (net zoals bakkerls en ik vermoed ook RobIII's oplossing) fout gaat rond de jaarwisseling. Volgens de manual heeft DAYOFYEAR de range 1-366, dus wat wellicht werkt is

code:

SELECT     *, FROM_UNIXTIME(bday) as bddate
FROM       kalender
WHERE      bddate IN (NOW(), NOW() + INTERVAL 2 WEEKS)
ORDER BY   DAYOFYEAR(bddate - NOW())

effectief zorg je er dan voor dat je een underflow triggert die de eerste 14 dagen goed zet - maar of dit werkt of een error geeft (of zelfs gewoon 1 returned voor de eerste twee weken) weet ik niet en mag je zelf gaan testen

[ Voor 22% gewijzigd door FragFrog op 27-08-2006 04:34 ]