MySql - Order by date and then by time

AndreMiranda_60286 0
Viewed 80088

I have a table with 2 'datetime' fields: 'eventDate' and 'eventHour'. I'm trying to order by 'eventDate' and then by 'eventHour'.

For each date I will have a list os events, so that's why I will to order by date and then by time.

thanks!!

sql-order-by datetime mysql
9 Answers

why not to use a TIMESTAMP to join the both fields?

First, we need to convert the eventDate field to DATE, for this, we'll use the DATE() function:

SELECT DATE( eventDate );

After that, convert the eventHour to TIME, something like this:

SELECT TIME( eventHour );

The next step is to join this two functions into a TIMESTAMP function:

SELECT TIMESTAMP( DATE( eventDate ), TIME( eventHour ) );

Yeah, but you don't need a SELECT, but an ORDER BY, so a complete example will be like this:

SELECT e.*, d.eventDate, t.eventHour
FROM event e
JOIN eventDate d
     ON e.id = d.id
JOIN eventTime t
     ON e.id = t.id
ORDER BY TIMESTAMP( DATE( d.eventDate ), TIME( t.eventHour ) );

I hope this can help you.

user_118049340
Related