{"id":178,"date":"2014-02-18T10:33:04","date_gmt":"2014-02-18T15:33:04","guid":{"rendered":"http:\/\/signaturewebmedia.com\/blog\/?p=178"},"modified":"2014-02-18T10:33:04","modified_gmt":"2014-02-18T15:33:04","slug":"mysql-order-by-date-type-issue","status":"publish","type":"post","link":"http:\/\/signaturewebmedia.com\/blog\/178\/mysql-order-by-date-type-issue\/","title":{"rendered":"MySQL Order By date type issue"},"content":{"rendered":"

<\/a><\/p>

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.<\/p>\n

My original table structure and its data:<\/p>\n

mysql> describe holidays;\r\n+-----------------+--------------+------+-----+---------+----------------+\r\n| Field           | Type         | Null | Key | Default | Extra          |\r\n+-----------------+--------------+------+-----+---------+----------------+\r\n| holidayId       | int(11)      | NO   | PRI | NULL    | auto_increment |\r\n| holidayName     | varchar(255) | NO   |     | NULL    |                |\r\n| holidayDate     | date         | NO   |     | NULL    |                |\r\n| holidayDuration | int(11)      | NO   |     | NULL    |                |\r\n+-----------------+--------------+------+-----+---------+----------------+<\/pre>\n
mysql> select holidayId, holidayName, holidayDate from holidays;\r\n+-----------+-------------------+-------------+\r\n| holidayId | holidayName       | holidayDate |\r\n+-----------+-------------------+-------------+\r\n| 1         | Memorial Day      | 2014-05-26  |\r\n| 2         | Independence Day  | 2014-07-04  |\r\n| 3         | Labor Day         | 2014-09-01  |\r\n| 4         | Thanks Giving Day | 2014-11-27  |\r\n| 5         | Christmas Day     | 2014-12-25  |\r\n| 6         | New Year's Day    | 2015-01-01  |\r\n+-----------+-------------------+-------------+<\/pre>\n
Then I’d query the table with the ORDER BY clause with some DATE_FORMAT():<\/p>\n
mysql> SELECT holidayId, holidayName, date_format(holidayDate,'%m\/%d\/%Y') holidayDate\r\n -> FROM holidays\r\n -> ORDER BY holidayDate;\r\n+-----------+-------------------+-------------+\r\n| holidayId | holidayName       | holidayDate |\r\n+-----------+-------------------+-------------+\r\n| 6<\/span><\/strong>         | New Year's Day<\/strong><\/span>    | 01\/01\/2015<\/span><\/strong>  |\r\n| 1         | Memorial Day      | 05\/26\/2014  |\r\n| 2         | Independence Day  | 07\/04\/2014  |\r\n| 3         | Labor Day         | 09\/01\/2014  |\r\n| 4         | Thanks Giving Day | 11\/27\/2014  |\r\n| 5         | Christmas Day     | 12\/25\/2014  |\r\n+-----------+-------------------+-------------+<\/pre>\n
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.<\/p>\n
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.”<\/p>\n
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:<\/p>\n
mysql> SELECT date_format(holidayDate,'%m\/%d\/%Y')\r\n -> FROM holidays;\r\n+-------------------------------------+\r\n| date_format(holidayDate,'%m\/%d\/%Y')<\/span><\/strong> |\r\n+-------------------------------------+\r\n| 05\/26\/2014                          |\r\n| 07\/04\/2014                          |\r\n| 09\/01\/2014                          |\r\n| 11\/27\/2014                          |\r\n| 12\/25\/2014                          |\r\n| 01\/01\/2015                          |\r\n+-------------------------------------+<\/pre>\n
===> it became \"date_format(holidayDate,'%m\/%d\/%Y')\"<\/pre>\n
Thus my workaround is (I want to use alias!):<\/p>\n
mysql> SELECT holidayId, holidayName, date_format(holidayDate,'%m\/%d\/%Y') 'Holiday Date'<\/span>\r\n -> FROM holidays\r\n -> ORDER BY holidayDate<\/span><\/strong>;\r\n+-----------+-------------------+--------------+\r\n| holidayId | holidayName       | Holiday Date |\r\n+-----------+-------------------+--------------+\r\n|         1 | Memorial Day      | 05\/26\/2014   |\r\n|         2 | Independence Day  | 07\/04\/2014   |\r\n|         3 | Labor Day         | 09\/01\/2014   |\r\n|         4 | Thanks Giving Day | 11\/27\/2014   |\r\n|         5 | Christmas Day     | 12\/25\/2014   |\r\n|         6 | New Year's Day    | 01\/01\/2015   |\r\n+-----------+-------------------+--------------+<\/pre>\n","protected":false},"excerpt":{"rendered":"
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.
\nMy original table structure and its data:
\nmysql> describe holidays;
\n+—————–+————–+——+—–+———+—————-+
\n| Field           | Type         | Null | Key | Default | Extra          |
\n+—————–+————–+——+—–+———+—————-+
\n| holidayId       | int(11)      | NO   | PRI | NULL    | auto_increment |
\n| holidayName     | varchar(255) | NO   |     | NULL    |                |
\n| holidayDate     | date         | NO   |     | NULL    |                |
\n| holidayDuration | int(11)      | NO   |     | NULL    |                |
\n+—————–+————–+——+—–+———+—————-+
\nmysql> select holidayId, holidayName, holidayDate from holidays;
\n+———–+——————-+————-+
\n| holidayId | holidayName       | holidayDate |
\n+———–+——————-+————-+
\n| 1         | Memorial Day      | 2014-05-26  |
\n| 2         | Independence Day  | 2014-07-04  |
\n| 3         | Labor Day         | 2014-09-01  |
\n| 4         | Thanks Giving Day | 2014-11-27  |
\n| 5         | Christmas Day     | 2014-12-25  |
\n| 6         | New Year’s Day    | 2015-01-01  |
\n+———–+——————-+————-+
\nThen I’d query the table with the ORDER BY clause with some DATE_FORMAT():
\nmysql> SELECT holidayId, holidayName, date_format(holidayDate,’%m\/%d\/%Y’) holidayDate
\n -> FROM holidays
\n -> ORDER BY holidayDate;
\n+———–+——————-+————-+
\n| holidayId | holidayName       | holidayDate |
\n+———–+——————-+————-+
\n| 6         | New Year’s Day    | 01\/01\/2015  |
\n| 1         | Memorial Day      | 05\/26\/2014  |
\n| 2         | Independence Day  | 07\/04\/2014  |
\n| 3         | Labor Day         | 09\/01\/2014  |
\n| 4         | Thanks Giving Day | 11\/27\/2014  |
\n| 5         | Christmas Day     | 12\/25\/2014  |
\n+———–+——————-+————-+
\nAs 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.<\/p>\n
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.”
\nThus, 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:
\nmysql> SELECT date_format(holidayDate,’%m\/%d\/%Y’)
\n -> FROM holidays;
\n+————————————-+
\n| date_format(holidayDate,’%m\/%d\/%Y’) |
\n+————————————-+
\n| 05\/26\/2014                          |
\n| 07\/04\/2014                          |
\n| 09\/01\/2014                          |
\n| 11\/27\/2014                          |
\n| 12\/25\/2014                          |
\n| 01\/01\/2015                          |
\n+————————————-+
\n===> it became “date_format(holidayDate,’%m\/%d\/%Y’)”
\nThus my workaround is (I want to use alias!):
\nmysql> SELECT holidayId, holidayName, date_format(holidayDate,’%m\/%d\/%Y’) ‘Holiday Date’
\n -> FROM holidays
\n -> ORDER BY holidayDate;
\n+———–+——————-+————–+
\n| holidayId | holidayName       | Holiday Date |
\n+———–+——————-+————–+
\n|         1 | Memorial Day      | 05\/26\/2014   |
\n|         2 | Independence Day  | 07\/04\/2014   |
\n|         3 | Labor Day         | 09\/01\/2014   |
\n|         4 | Thanks Giving Day | 11\/27\/2014   |
\n|         5 | Christmas Day     | 12\/25\/2014   |
\n|         6 | New Year’s Day    | 01\/01\/2015   |
\n+———–+——————-+————–+<\/p>\n
\r\n\r\n<\/a><\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false},"categories":[21],"tags":[175,190,185,192,180,178,188,186,193,191,187,189,181,183,179,176,182,177],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/posts\/178"}],"collection":[{"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/comments?post=178"}],"version-history":[{"count":7,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/posts\/178\/revisions"}],"predecessor-version":[{"id":185,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/posts\/178\/revisions\/185"}],"wp:attachment":[{"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/media?parent=178"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/categories?post=178"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/tags?post=178"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}