title: Comparing Time extracted from a Datetime column with a Static time in MariaDB and MySQL
body: <p>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:</p>
  
  <pre><code>SELECT sender_id, SUM(CASE WHEN DATE_FORMAT(created_at,'%H:%i:%s')&gt;CAST('17:00:00' AS time) THEN 1 ELSE 0 END) AS no_msgs_afterhours GROUP BY sender_id;
  </code></pre>
  
  <p>The funny thing is this works perfectly in the MariaDB database instance but in the MySQL instance it gives me this error:</p>
  
  <blockquote>
    <p>ERROR 1267 (HY000): Illegal mix of collations
    (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for
    operation '>'</p>
  </blockquote>
  
  <p>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. </p>