MySql - order by date and then by time

Question

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

For each date, I will have a list of os events, so I will order by date and then by time.

thanks!!

Answer 1

I'm not sure if there is any hidden meaning in your question, but the standard way to do this seems to be consistent:

... order by eventDate, eventHour 

This gives you hours for dates, for example:

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

If you have these two fields as real datetime fields, your circuit is screwed. You must have a date field for date and time or an entire field for an hour.

You could combine both into a single datetime field, but you should balance this with the inefficiency of each row in your select statements if you want to separate them. It is usually best to keep the fields separate if you intend to use them distinctly.

If you need to use per-row functions, you can use:

 date(datetime_column) time(datetime_column) 

to extract only the date and time a datetime components.

Answer 2

If you want to transfer data first as the last date of the event, if records of the same day exist, then the data will be sorted by time, i.e. most recently

You can try as below

 select * from my_table order by eventDate DESC, eventHour DESC 

Answer 3

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

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

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

Answer 4

whether

 select * from my_table order by eventDate, eventHour 

does not work?

Answer 5

You can order as much as you need, just use the list. He will order the first value, then the second ... etc.

  SELECT * FROM table ORDER BY eventDate, eventHour 

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

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

Answer 6

Order it by id in descending order.

<?php $sql = "SELECT * FROM posts ORDER BY id DESC?>

This should work

Answer 7

Firstly, it’s bad practice to keep the date and time in different fields.

In any case, if you save the date / time in the appropriate format. (i.e., the date format is yyyy-mm-dd, and the time is hh: ss)

First you need to combine the date and time to get the datetime value. From a technical point of view, you can do ORDER BY ON in the Datetime field, but this is not a good practice. In our case, when we run CONCAT, this is converted to a string, so the result will be incorrect. Therefore, you need to convert it to UNIX TIMESTAMP in order to place an order.

It is good practice to run UNIX_TIMESTAMP in the datetime field before the ORDER by clause.

You can use the following query.

SELECT column1, column2, UNIX_TIMESTAMP (CONCAT ( event_date , '', event_time )) as unixtimestamp from_name_name Order by unixtimestamp desc;

Note. (The CONCAT function has a space between event_date and event_time. It does not display correctly here.)

Answer 8

This is the best way that worked for me: just convert the date to the number of days with the to_days function (date) and the time to the number of seconds with the TIME_TO_SEC function (time) , for example:

 SELECT nv.destac, DATE_FORMAT(nv.date_nov , '%d-%m-%Y %T') as date_order, TO_DAYS(nv.date_nov) as days, TIME_TO_SEC(TIME(nv.date_nov)) as seconds FROM newsviews nv WHERE nv.active ORDER BY nv.destac DESC, days DESC, seconds DESC ; 

A relay measure, consisting in the fact that it was aimed at convincing me that she was free. TO_DAYS (fecha) y el tiempo a la cantidad de segundos con la funcion TIME_TO_SEC (tiempo)

 SELECT nv.destacar, DATE_FORMAT(nv.fecha_nov , '%d-%m-%Y %T') as fecha_orden, TO_DAYS(nv.fecha_nov) as dias, TIME_TO_SEC(TIME(nv.fecha_nov)) as segundos FROM novedades nv WHERE nv.activa ORDER BY nv.destacar DESC, dias DESC, segundos DESC ; 

Answer 9

why not use TIMESTAMP to combine both fields?

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

 SELECT DATE( eventDate ); 

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

 SELECT TIME( eventHour ); 

The next step is to combine these two functions into a TIMESTAMP function:

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

Yes, but you do not need SELECT, but ORDER BY, so the full example would be:

 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 helps you.