Comparing Time extracted from a Datetime column with a Static time in MariaDB and MySQL

Question

I have a database with a bunch of messages sent between a set of people and each message has a timestamp in a date-time format. I'm extracting the time from this date-time column and attempting to compare it with a static timestamp to see how many messages were sent after that time by each user. The code I'm using for that is as follows:

SELECT sender_id, SUM(CASE WHEN DATE_FORMAT(created_at,'%H:%i:%s')>CAST('17:00:00' AS time) THEN 1 ELSE 0 END) AS no_msgs_afterhours GROUP BY sender_id;

The funny thing is this works perfectly in the MariaDB database instance but in the MySQL instance it gives me this error:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>'

The MariaDB instance is version 5.5.32 and the MySQL instance is 5.5.29. Could this be a MySQL version issue? I read online that there used to be similar collation issues with MySQL 4.1.8 and earlier but that shouldn't apply here.

Have the server instances got different default collations? What's the output of SHOW VARIABLES LIKE '%colla%'; on each server? — Jeremy Smyth
Both server instances have the same default collations: collation_connection = utf8_general_ci, collation_database = utf8_general_ci, collation_server = latin1_swedish_ci — gorangutan

fancyPants fancyPants · Accepted Answer · 2013-09-09T19:14:11

It's because you have different default collations for server and database / connection. The DATE_FORMAT() function produces a latin1_swedish_ci collation and compares it with the database collation.

Write it like this and it will work:

SELECT sender_id, SUM(CASE WHEN TIME(created_at) > CAST('17:00:00' AS time) THEN 1 ELSE 0 END) AS no_msgs_afterhours GROUP BY sender_id;

Note: You're missing a FROM clause ;)

You don't even need to cast as time:

SELECT sender_id, SUM(CASE WHEN TIME(created_at) > '17:00:00' THEN 1 ELSE 0 END) AS no_msgs_afterhours GROUP BY sender_id;

You can see how it works with this little test script:

create table foo (dt datetime, ts timestamp);
insert into foo values (NOW(), NOW());

/*doesn't work*/
SELECT 
CASE WHEN DATE_FORMAT(ts,'%H:%i:%s')>CAST('17:00:00' AS time) THEN 1 ELSE 0 END 
FROM foo;

/*works*/
SELECT 
CASE WHEN DATE_FORMAT(ts,'%H:%i:%s')>'17:00:00'  THEN 1 ELSE 0 END 
FROM foo;

/*works*/
SELECT 
CASE WHEN TIME(ts)>'17:00:00' THEN 1 ELSE 0 END 
FROM foo;

/*works*/
SELECT 
CASE WHEN TIME(ts)>CAST('17:00:00' AS time) THEN 1 ELSE 0 END 
FROM foo;

Comparing Time extracted from a Datetime column with a Static time in MariaDB and MySQL

1 Answers