MySQL sort by 4 fields date and time

I have a table where there are 2 date columns and 2 time columns (arrival date, arrival time, departure date, departure time)

I want to combine the 2 date fields in one and then sort by date, arrival time, departure time.

So what I want to achieve is display the arrivals and departures of the following x days in order.

From This:

+--------------+--------------+----------------+----------------+--+
| arrival date | arrival time | departure date | departure time |  |
+--------------+--------------+----------------+----------------+--+
| 20-06-21     | 06:00        | NULL           | NULL           |  |
| 19-06-21     | 08:00        | 24-06-21       | 08:00          |  |
| NULL         | NULL         | 20-06-21       | 08:00          |  |
+--------------+--------------+----------------+----------------+--+

To this:

+----------+--------------+----------------+
| date     | arrival time | departure time |
+----------+--------------+----------------+
| 19-06-21 | 08:00        | NULL           |
+----------+--------------+----------------+
| 20-06-21 | 06:00        | NULL           |
+----------+--------------+----------------+
| 20-06-21 | NULL         | 08:00          |
+----------+--------------+----------------+
| 24-06-21 | NULL         | 08:00          |
+----------+--------------+----------------+

This is my query right now:

SELECT * FROM `bookings` WHERE STR_TO_DATE(`arrivalDate`, '%d-%m-%y') >= NOW() 
AND STR_TO_DATE(`arrivalDate`, '%d-%m-%y') <= DATE(DATE_ADD(NOW(), INTERVAL +20 DAY))  
OR STR_TO_DATE(`departureDate`, '%d-%m-%y') >= NOW() AND STR_TO_DATE(`departureDate`, '%d-%m-%y') <= DATE(DATE_ADD(NOW(), INTERVAL +20 DAY))
ORDER BY STR_TO_DATE(`arrivalDate`, '%d-%m-%y') asc, 
STR_TO_DATE(`arrivalTime`, '%h:%i') asc,  
STR_TO_DATE(`departureDate`, '%d-%m-%y') asc,  
STR_TO_DATE(`departureTime`, '%h:%i') asc

Answer

You first need to split arrival and departure times into two rows (e.g. in your data the 3 rows become 4). Only then you can sort them:

SELECT *
FROM (
    SELECT arrivalDate AS date, arrivalTime, NULL AS departureTime
    FROM t
    WHERE arrivalDate IS NOT NULL

    UNION ALL

    SELECT departureDate, NULL, departureTime
    FROM t
    WHERE departureDate IS NOT NULL
) AS sq1
ORDER BY date, COALESCE(arrivalTime, departureTime)

In the above example you must replace dates strings with STR_TO_DATE(...).

Demo on db<>fiddle

Advertisement

Answer