mysql ORDER BY "month" with unixtime

I have some dates/events in a database, and I'd like to pull them out ordered by month (year doesn't matter) - right now all the timestamps are in unix in a column named eventDate. How can make that query?

SELECT * FROM cal开发者_JS百科endar ORDER BY eventDate

Obviously that sorts them, but I want to make sure all events across all years are grouped by month - then obviously need to arrange them January, February, March, etc.

Any advice?

Thanks!

You could use FROM_UNIXTIME() function + MONTH() function.

SELECT MONTH(FROM_UNIXTIME(0));
-- 12

But there's no reason to store a unix timestamp over a real timestamp (YYYY-MM-DD HH:II:SS). RDBMS have functions to manipulate dates and if you really need the unix timestamp (I never do, TBH), you can use the UNIX_TIMESTAMP function.

There are plenty of extremely good reasons for using unix time. Good database design hugely impacts how expensive it is to run databases and website, especially successful busy ones.

The best case I know of is.. a really busy server(s) and where time data is required to be stored but the time data is actually accessed rarely compared to the number of reads and writes actually going on in the db. It takes cpu resources to do all the manipulation of that time data, So don't unless you absolutely have to.

A real life example is my own. We needed 4 front end web servers and were going to be adding more. they were old too and needed updating. looking at 6 replacement servers that would be needed it was going to cost us a bundle. decided to look about what we were doing. We now have 2 front end servers instead of 4 or 6. what it took? optimizing the database structure and queries and the code that inserted and read data from them.

One example that took your exact consideration in mind... changed 1 line of php code, changed the time column to unix instead of yyyy-dd-mm hh:mm:ss, added an index to the time column and that one operation went from 0.08 seconds to 0.00031 seconds start to finish. The multifold impact on cpu resources was huge. the next queued up operations executed faster... etc.

That is why people have jobs as database designers... it really is important.

of course if your website is slow and not busy.. probably no one will notice. But if you are successfull, it WILL matter. If you've got a busy site and your servers get sluggish... look at things like this. You might not need a new box or more memmory, you just might need to clean up code and optimize the db.

Timestamps, their form and how they are used and stored DO MATTER.