MySQL Order By date type issue
This must be the “gotcha” type of stuff when you think just using the “ORDER BY <date type>” clause would give you back the correct results in MySQL.
My original table structure and its data:
mysql> describe holidays; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | holidayId | int(11) | NO | PRI | NULL | auto_increment | | holidayName | varchar(255) | NO | | NULL | | | holidayDate | date | NO | | NULL | | | holidayDuration | int(11) | NO | | NULL | | +-----------------+--------------+------+-----+---------+----------------+
mysql> select holidayId, holidayName, holidayDate from holidays; +-----------+-------------------+-------------+ | holidayId | holidayName | holidayDate | +-----------+-------------------+-------------+ | 1 | Memorial Day | 2014-05-26 | | 2 | Independence Day | 2014-07-04 | | 3 | Labor Day | 2014-09-01 | | 4 | Thanks Giving Day | 2014-11-27 | | 5 | Christmas Day | 2014-12-25 | | 6 | New Year's Day | 2015-01-01 | +-----------+-------------------+-------------+
Then I’d query the table with the ORDER BY clause with some DATE_FORMAT():
mysql> SELECT holidayId, holidayName, date_format(holidayDate,'%m/%d/%Y') holidayDate -> FROM holidays -> ORDER BY holidayDate; +-----------+-------------------+-------------+ | holidayId | holidayName | holidayDate | +-----------+-------------------+-------------+ | 6 | New Year's Day | 01/01/2015 | | 1 | Memorial Day | 05/26/2014 | | 2 | Independence Day | 07/04/2014 | | 3 | Labor Day | 09/01/2014 | | 4 | Thanks Giving Day | 11/27/2014 | | 5 | Christmas Day | 12/25/2014 | +-----------+-------------------+-------------+
As you can see, the New Year’s Day for 2015 is at top. It seems like the records were sorted based on the numbers and not by the chronological orders.
According to the manual (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format), the DATE_FORMAT() would return “a string.”
Thus, the correct way to sort the date type column is to sort by the original column name. In our case, the original column name was aliased/renamed as “holidayDate.” If we did not alias this, it would have been:
mysql> SELECT date_format(holidayDate,'%m/%d/%Y')
-> FROM holidays;
+-------------------------------------+
| date_format(holidayDate,'%m/%d/%Y') |
+-------------------------------------+
| 05/26/2014 |
| 07/04/2014 |
| 09/01/2014 |
| 11/27/2014 |
| 12/25/2014 |
| 01/01/2015 |
+-------------------------------------+
===> it became "date_format(holidayDate,'%m/%d/%Y')"
Thus my workaround is (I want to use alias!):
mysql> SELECT holidayId, holidayName, date_format(holidayDate,'%m/%d/%Y') 'Holiday Date' -> FROM holidays -> ORDER BY holidayDate; +-----------+-------------------+--------------+ | holidayId | holidayName | Holiday Date | +-----------+-------------------+--------------+ | 1 | Memorial Day | 05/26/2014 | | 2 | Independence Day | 07/04/2014 | | 3 | Labor Day | 09/01/2014 | | 4 | Thanks Giving Day | 11/27/2014 | | 5 | Christmas Day | 12/25/2014 | | 6 | New Year's Day | 01/01/2015 | +-----------+-------------------+--------------+
phpMyAdmin login/logout
If you are wondering where the logout link for phpMyAdmin in your installation is, most likely you have the automatic login configured (auth_type is “config”) in your configuration file, config.in.php.
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'admin_user';
$cfg['Servers'][$i]['password'] = 'password';
Just change the auth_type to “cookie” and you will be redirected to login page when refresh the phpMyAdmin session and you will see the logout link. 🙂
MySQL: case insesitive “GROUP BY”
When we were building some SQL statements to gather some statistics and realized that the “GROUP BY” clause is NOT case sensitive on MySQL… (we are using utf8_unicode_ci as a character set collation)
i.e.
mysql> select keyword, count(*) from TopKeywords
-> where lower(keyword) like ‘secured loans’
-> group by keyword;+—————+———-+
| keyword      | count(*) |
+—————+———-+
| secured loans |Â Â Â Â Â Â Â 4 |
+—————+———-+
1 row in set (0.50 sec)I know the max count(*) result should be 3 if the key is case sensitive.
We found that this note: http://bugs.mysql.com/bug.php?id=24838
According to the note above, it is NOT a bug; however, it does not seem like it is giving a workaround.
Scratching our head a little and found a easy solution if you need to use the case sensitive GROUP BY clause. The thing is that you need to group by absolutely unique value. In our case, used hex() function:
mysql> select keyword, hex(keyword), count(*) from TopKeywords
-> where lower(keyword) like ‘secured loans’
-> group by hex(keyword);+—————+—————————-+———-+
| keyword      | hex(keyword)              | count(*) |
+—————+—————————-+———-+
| Secured Loans | 53656375726564204C6F616E73 |Â Â Â Â Â Â Â 1 |
| secured loans | 73656375726564206C6F616E73 |Â Â Â Â Â Â Â 3 |
+—————+—————————-+———-+
2 rows in set (0.48 sec)
We are sure that you can use some other functions to achieve case sensitive group by effects.