CODEWITHSUNDEEP
mysql
i-CONICA
i-CONICA

Reputation: 2379

MySQL Months stored as strings, how to order chronologically, rather than alphabetically?

I've got months stored in a table like this "December, August, July", etc. How can I order my query so that they're ordered chronologically rather than alphabetically? I probably need to convert from string to time to get a number then order by that, I can do this in PHP but don't know how to do this directly within the query so I don't have to make a query, use PHP to manipulate some data, then make another query using that.

Can MySQL do this in a query?

Thanks.

Upvotes: 0

Views: 1406

Answers (6)

Vijay Srinivas
Vijay Srinivas

Reputation: 81

Try this:

SELECT * FROM tablename ORDER BY FIELD(month_column,
'December', 'November', 'October',
'September', 'August', 'July',
'June', 'May', 'April',
'March', 'February', 'January') DESC, month_column ASC

Hope this would help.

Upvotes: 0

mmmmmm
mmmmmm

Reputation: 32710

What I would do is create another table

CREATE TABLE MONTH_NAME
( 
    MONTH_NAME VARCHAR, // or whatever type the month is in your db
    MONTH_NUMBER INT
)

Fill with "January", 1 etc

Then in the query join the two tables here in on the month name and order by the MONTH_NUMBER.

Upvotes: 3

ajreal
ajreal

Reputation: 47321

Yes, you will hate this :-

order by 
case month_column
  when "January" then 1
  when "February" then 2
  ...
  when "December" then 12
end case

There is another str_to_date function in mysql

mysql> select str_to_date('January', '%M');
+------------------------------+
| str_to_date('January', '%M') |
+------------------------------+
| 0000-01-00                   |
+------------------------------+

--> sorting
order by str_to_date(column_name, '%M')

Since you not going to provide other information (year,day, time),
so this function will work too.

The best way is convert all the string into small integer,
which is the correct way and have the best optimization

Upvotes: 1

konsolenfreddy
konsolenfreddy

Reputation: 9671

You can use a CASE statement:

SELECT 
CASE 
    WHEN `month` = 'January' THEN 1
    WHEN `month` = 'February' THEN 2
    WHEN `month` = 'March' THEN 3
    WHEN `month` = 'April' THEN 4
    WHEN `month` = 'May' THEN 5
    ....
END AS orderbymonth
FROM yourtable
ORDER BY orderbymonth ASC

Upvotes: 1

themhz
themhz

Reputation: 8424

I would add a column in the table db type integer. Then i would create another reference table with the monthes Col1 Name:January Col2 Value:1:Col1 Name:February Col2 Value:2 .. etc Then i would inner join with the names, and order by the value. Works 100%

Upvotes: 0

Steve Tjoa
Steve Tjoa

Reputation: 61094

If you have a months table, then you can order by month_number.

select field from tablename join months using (month_name) 
  order by month_number asc;

e.g. 

insert into months (month_name, month_number) 
  values ('January', 1), ('February', 2), ...

Upvotes: 0

Related Questions