[MySql] Order by datum

[Currahee]

Sinds enige tijd werk ik aan een systeem waarbij reizen opgeslagen kunnen worden in een MySql database. Hierbij wordt de vertrek_datum opgeslagen door middel van een mktime() functie, waarbij zowel datum als tijd (uren, minuten) worden ingevoerd. De mysql database is als volgt ingedeeld:

Reizen

• reisid
• ledenid
• vertrek_datum (mktime)

Autoreizen

• reisid
• afstand

Momenteel ben ik bezig grafieken te maken van de reizen. Daarvoor moeten dus de reizen uit de database worden gehaald, waarbij de reizen gegroepeerd worden op dag. De afstand van de verschillende reizen op dezelfde dag worden dan opgeteld. Dit doe ik door middel van de volgende sql query:

SQL:

SELECT reizen.datum_vertrek
, SUM(autoreizen.afstand) AS afstand
FROM reizen
LEFT JOIN autoreizen
ON reizen.reisid = autoreizen.reisid                        
WHERE reizen.ledenid='".$ledenid."'
GROUP BY reizen.datum_vertrek
ORDER BY reizen.datum_vertrek DESC;

Het probleem waar ik momenteel tegen aanloop is dat bovenstaande query de reizen groepeert op zowel datum als tijd. 23 juli 2010 18:00 uur en 23 juli 19:00 uur zijn dus aparte resultaten, in plaats van dat hij deze samenvoegt. Na een zoektocht op google vond ik de oplossing in de vorm van de mysql functies DAY(), MONTH() en YEAR(). Helaas heb ik in mijn database de datums en tijd opgeslagen door middel van de mktime() functie en niet door middel van timestamp(), waardoor deze oplossing volgens mij niet opgaat.

Voordat ik mijn hele database overhoop ga trekken, is mijn vraag daarom ook of er een optie is om te groeperen op dag met het mktime() datum formaat. Alle suggesties zijn welkom, want ik zie momenteel de oplossing niet meer

Edit: Na het posten zie ik dat de titel niet klopt. Zou een mod die kunnen aanpassen naar group by datum? Alvast bedankt!

[ Voor 3% gewijzigd door Currahee op 23-07-2010 19:30 ]

[hellfighter87]

al de functie date() geprobeerd?

SQL:

SELECT reizen.datum_vertrek
, SUM(autoreizen.afstand) AS afstand
FROM reizen
LEFT JOIN autoreizen
ON reizen.reisid = autoreizen.reisid                        
WHERE reizen.ledenid='".$ledenid."'
GROUP BY DATE(reizen.datum_vertrek)
ORDER BY reizen.datum_vertrek DESC;

[ Voor 83% gewijzigd door hellfighter87 op 23-07-2010 19:40 ]

[kluyze]

Volgens mij kunnen de meeste sql functies om met een epoch tijd, ik heb het niet getest maar dat kan je makkelijk zelf. Gewoon een datum opzoeken en de query "SELECT DAY(1279906856)"

Zou het niet gaan, mysql kent ook de functie FROM_UNIXTIME()

edit:
Toch even getest en day kan niet om met epoch maar "SELECT DAY(FROM_UNIXTIME(1279906856))" werkt wel.

[ Voor 18% gewijzigd door kluyze op 23-07-2010 19:44 ]

[Currahee]

kluyze schreef op vrijdag 23 juli 2010 @ 19:41:
Zou het niet gaan, mysql kent ook de functie FROM_UNIXTIME()

edit:
Toch even getest en day kan niet om met epoch maar "SELECT DAY(FROM_UNIXTIME(1279906856))" werkt wel.

Bedankt voor je hulp! FROM_UNIXTIME() was inderdaad de oplossing die ik zocht.

SQL:

SELECT reizen.datum_vertrek
, SUM(autoreizen.afstand) AS afstand
LEFT JOIN autoreizen
WHERE reizen.ledenid='".$ledenid."'
GROUP BY DAY(FROM_UNIXTIME(reizen.datum_vertrek))
ORDER BY reizen.datum_vertrek DESC;

Ben je dan een paar uur naar op zoek

[ Voor 22% gewijzigd door Currahee op 23-07-2010 19:51 ]

[kluyze]

Wat je ook kan doen, je weet dat er in een dag 86400 seconden zitten, als je groepeert op de integer waarde van epoch/86400 dat zou ook per dag moeten groeperen.
Ik weet niet wat voor de database sneller is, met integers werken is normaal sneller als met datetime waarden moeten rekenen. Maar MySQL kan wel eens vreemd doen.

zoiets dan bv:
SELECT FLOOR(1279906856/86400)

Ik heb ook CAST getest, maar om een of ander gekke reden gaat CAST in MySQL het resultaat afronden. Normaal (in elke andere taal die ik gebruikt heb) gaat een cast alleen het geheel deel van de variabele nemen, maar in MySQL gaat een getal dat hoger of gelijk uitkomt als .5 naar boven afgerond worden. CAST(0.5 AS SIGNED) geeft dus gek genoeg 1.

[ Voor 3% gewijzigd door kluyze op 23-07-2010 20:07 ]

[Voutloos]

kluyze schreef op vrijdag 23 juli 2010 @ 20:07:
Wat je ook kan doen, je weet dat er in een dag 86400 seconden zitten, als je groepeert op de integer waarde van epoch/86400 dat zou ook per dag moeten groeperen.
Ik weet niet wat voor de database sneller is, met integers werken is normaal sneller als met datetime waarden moeten rekenen.

Klassieke fout, denken dat je zelf 'even snel' met data/tijden kan rekenen. Je gaat grandioos de mist in dankzij DST.

Zorg dat je de geschikte datatypes en bijbehordende functies gebruikt. Zelf rekenen met tijd == gegarandeerd falen.

[kluyze]

Als je zorgt dat bij het wegschrijven je tijd in UTC staat, kan er weinig mis gaan lijkt me. Een van de bedrijven waar ik werkte telde dikwijls ook zelf met tijden in epoch en daar was de tijd kritisch genoeg dat mensen moeten inloggen op oudejaar om er voor te zorgen/controleren dat hun systeem niet de mist in ging met de leap second.

[NMe]

Voutloos schreef op vrijdag 23 juli 2010 @ 20:10:
[...]
Klassieke fout, denken dat je zelf 'even snel' met data/tijden kan rekenen. Je gaat grandioos de mist in dankzij DST.

Zorg dat je de geschikte datatypes en bijbehordende functies gebruikt. Zelf rekenen met tijd == gegarandeerd falen.

DST is nog het minste probleem. Schrikkeljaren en zelfs schrikkelsecondes maken het helemaal leuk.