MySql - Order by date and then by time

Question

Hi!

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!!

Answer 1

I'm not sure whether there's any hidden meaning in your question but the standard way of doing this seems to fit:

... order by eventDate, eventHour

That gives you hours within dates, like:

Feb 15
    09:00
    12:00
    17:00
Feb 23
    22:00
    : :

If you actually have those two fields as real datetime fields, your schema is screwed up. You should have a date field for the date and a time or integral field for the hour.

You could combine both into a single datetime field but you should balance that against the inefficiencies of doing per-row functions in your select statements if you want to separate them. It's usually better to keep fields separate if your going to use them distinctly.

If you do have to use per-row functions, you can use:

date(datetime_column)
time(datetime_column)

to extract just the date and time components of a datetime.

Answer 2

You can order by as many as you need, just use a list. It will order by the first value, then the second...etc.

  SELECT *
    FROM table
ORDER BY eventDate, eventHour

If you have two datetime fields then you can get just the date or just the hour using something like:

    SELECT *
      FROM table
  ORDER BY DATE_FORMAT(eventDate, '%d'), DATE_FORMAT(eventHour, '%h')

Answer 3

Why not save both the eventDate and eventHour into the same field if they are both DateTime ?

To get what you want with the current scheme you can do this:

SELECT * FROM table 
  ORDER BY 
    EXTRACT(YEAR_MONTH FROM eventDate),
    EXTRACT(DAY FROM eventDate),
    EXTRACT(HOUR FROM eventHour)

Answer 4

Does

select * from my_table order by eventDate, eventHour

not work?