Query to order time events showing first future events and after show current events

Question

I have a table where i store events and it has 2 fields for dates. One for the start date (start_date) of the event and other for the end date (end_date). Some events dont have end_date defined or have end_date='1970-01-01'.

I used the following mysql query to retreive the current and future events.

SELECT * 
FROM events 
WHERE start_time >= CURDATE() 
    OR (start_time <= CURDATE() AND end_time>=CURDATE()) 
ORDER BY events.start_time;

What i would need is a query that returns the same results but ordered in a different way and according to the following way:

• the events are 'divided' in two groups: the current events, and the future events. Current events are considered all the events that started in the day before the actual date ('yesterday') and still didn't finished.

• the results will show first future events, ordered ascending by start_time, and after show current events, ordered ascending by start_time.

Thanks

Answer 1

SELECT *,
       DATEDIFF(start_time, CURDATE()) AS diff
FROM   events
WHERE  start_time >= CURDATE()
        OR ( start_time <= CURDATE()
             AND end_time >= CURDATE() )
ORDER  BY CASE
            WHEN diff > 0 THEN 0
            ELSE 1
          end,
          diff ASC;