CODEWITHSUNDEEP
mysqlsqldatesql-order-by
user9991
user9991

Reputation: 63

How to order by dates with time periods with current date first then future time periods and then past time periods?

I have a table with columns ID, START_DATE and END_DATE. What I want to do is to execute a query which orders the resulting rows in the following order:

If the current date is 2020-12-14 then the result should be

START_DATE  END_DATE
2020-12-8   2020-12-18     -----> Time periods which contains current day and are ordered by start_date in ASCENDING order.
2020-12-9   2020-12-15     
2020-12-10  2020-12-17     
2020-12-15  2020-12-17     -----> Time periods with START DATE > current date and are ordered by start_date in ASCENDING order.
2020-12-16  2020-12-22
2020-12-21  2020-12-25
2020-12-9   2020-12-13     ----->Time periods with END DATE < current date and are ordered by start_date in DESCENDING order.
2020-12-6   2020-12-8
2020-12-1   2020-12-9

How can I accomplish this?

Upvotes: 0

Views: 391

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

In MySQL, I would write this as:

order by (curdate() between start_date and end_date) desc,
         (curdate() > curdate()) desc

case expressions are not needed because MySQL treats boolean values as a number, with 1 for true and 0 for false.

Upvotes: 1

Akina
Akina

Reputation: 42661

You need in something similar to

ORDER BY CASE WHEN CURRENT_DATE BETWEEN start_date AND end_date
              THEN 1
              WHEN start_date > CURRENT_DATE
              THEN 2
              ELSE 3 END,
         CASE WHEN end_date > CURRENT_DATE
              THEN start_date + 0
              ELSE DATEDIFF(CURRENT_DATE, start_date)
              END

fiddle

start_date + 0 needed for to convert the result of this CASE to numeric. Without it the CASE alternatives combines date and numeric values which causes the final datatype to be a string - with wrong sorting order.

Upvotes: 2

Popeye
Popeye

Reputation: 35910

You can use conditional ordering as follows:

Select * from your_table
Order by 
  case when CURRENT_DATE between start_date and end_date OR start_date > CURRENT_DATE 
       then 1
       When end_date < CURRENT_DATE 
       then 2 
  end,
  case when CURRENT_DATE between start_date and end_date or start_date > CURRENT_DATE 
       then date_diff(start_date, CURRENT_DATE) 
       When end_date < CURRENT_DATE 
       then date_diff(CURRENT_DATE,start_date) 
  end

Upvotes: 1

Related Questions