Bug #43099 MySQL does not sort datetime formatted in 12 hour format correctly

• Submitted: 23 Feb 2009 7:30
• Modified: 23 Feb 2009 7:51
• Reporter: S P
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: All
• OS: Any
• CPU Architecture: Any
• Tags: 12 hour format, comparison, sorting

[23 Feb 2009 7:30] S P

Description:
For a datetime 12 PM is less than 1 PM, 2 PM, ... However, MySQL will consider 12 PM greater than 1 PM, 2 PM, ... 11 PM. This is the problem.

Will be clear in the below example.

How to repeat:
Create table and insert below rows
----------------------------------
CREATE TABLE timesortingtest
(
	x 	int,
	time 	datetime
);

INSERT INTO timesortingtest
VALUES (1, "20090223121000");

INSERT INTO timesortingtest
VALUES (2, "20090223131000");

INSERT INTO timesortingtest
VALUES (3, "20090223141000");

This query will sort the rows correctly
---------------------------------------
SELECT x, time
FROM timesortingtest
ORDER BY time;

However, this query will not sort the rows correctly
----------------------------------------------------
SELECT x, DATE_FORMAT(time, "%r") formattedtime
FROM timesortingtest
ORDER BY formattedtime;

For a datetime 12 PM is less than 1 PM, 2 PM, ... However, MySQL will consider 12 PM greater than 1 PM, 2 PM, ... 11 PM. This is the problem.

Suggested fix:
Workaround: Simply sort using unformatted datetime
--------------------------------------------------
SELECT x, DATE_FORMAT(time, "%r") formattedtime
FROM timesortingtest
ORDER BY time;

[23 Feb 2009 7:51] Valeriy Kravchuk

This is not a bug. DATE_FORMAT returns string as the result, and stings are compared as strings, not as dates. See http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format for the details.