Date in MySQL

Today, I'm going to help my colleagues with a SQL (Simplified) execution error:

The code is as follows
Mysql> Select Date_format (' 2013-11-19 ', ' y-m-d ') > Timediff (' 2013-11-19 ', ' 2013-11-20 ');

ERROR 1267 (HY000): illegal mix of collations (utf8_general_ci,coercible) and (latin1_swedish_ci,numeric) for operation ' & gt; '

At first glance it is very baffling, check the manual, found that there are such a paragraph:

The language used for day and month names and abbreviations are controlled by the value of the Lc_time_names system variabl E (section 9.7, "MySQL Server Locale Support").

The Date_format () returns a string with a character set and collation given by Character_set_connection and Collation_conn Ection So, it can return month and weekday names containing Non-ascii.

That is, the date_formate () function returns the result with the character set/checksum set attribute, while the Timediff () function does not have a character set/checksum set attribute, so let's verify:

The code is as follows

Mysql> set names UTF8; Mysql> Select CharSet (date_format (' 2013-11-19 ', ' y-m-d ')), CharSet (' 2013-11-19 ', ' 2013-11-20 ')); +--------------------------------------------+-----------------------------------------------+ | CharSet ( Date_format (' 2013-11-19 ', ' y-m-d ') | CharSet (Timediff (' 2013-11-19 ', ' 2013-11-20 ') | +--------------------------------------------+-----------------------------------------------+ | UTF8                                          | binary                                          | +--------------------------------------------+-----------------------------------------------+ Mysql> set names gb2312; Mysql> Select CharSet (date_format (' 2013-11-19 ', ' y-m-d ')), CharSet (' 2013-11-19 ', ' 2013-11-20 ')); +--------------------------------------------+-----------------------------------------------+ | CharSet ( Date_format (' 2013-11-19 ', ' y-m-d ') | CharSet (Timediff (' 2013-11-19 ', ' 2013-11-20 ') | +--------------------------------------------+-----------------------------------------------+ | gb2312                                       | binary                                          | +--------------------------------------------+-----------------------------------------------+

The

can see that the character set for the Date_format () function returns the result is different, as the character_set_connection, collation_connection values are modified through the SET NAMES. In this case, if you want to work correctly, you need to make a character set conversion of the result, for example:

code is as follows

&nbs P;

mysql> Select Date_format (' 2013-11-19 ', ' y-m-d ') > convert (timedi FF (' 2013-11-19 ', ' 2013-11-20 ') using UTF8); +----------------------------------------------------------------------------------------------+ | date_ Format (' 2013-11-19 ', ' y-m-d ') > Convert (Timediff (' 2013-11-19 ', ' 2013-11-20 ') using UTF8) | +----------------------------------------------------------------------------------------------+ |                                                                                                 1 | +----------------------------------------------------------------------------------------------+

It's okay.

P.s,mysql version: 5.5.20-55-log percona Server (GPL), release rel24.1, Revision 217

Attached

The following is a description of the function's arguments:

%s,%s two-digit form of seconds (00, 01, ..., 59)

%i Two-digit form (00, 01, ..., 59)

%H two digits in the form of hours, 24 hours (00,01, ..., 23)

%h,%I Two digits in the form of hours, 12 hours (01,02, ..., 12)

%k digital form of hours, 24 hours (0,1, ..., 23)

%l digital form of hours, 12 hours (1, 2, ..., 12)

%T 24-hour time form (hh:mm:s s)

%r 12-hour time form (Hh:mm:ss AM or Hh:mm:ss PM)

%p AM or P M

%w the name of each day of the week (Sunday, Monday, ..., Saturday)

%a the name of each day of the week (Sun, Mon, ..., Sat)

%d two digits indicates the number of days in the month (00, 01, ..., 31)

%e numeric form represents days of the month (1, 2, ..., 31)

%d The English suffix indicates the number of days in the month (1st, 2nd, 3rd, ...)

%w represents the number of days in the week (0 = Sunday, 1=monday, ..., 6=saturday)

%j a three-digit number of days in the year (001, 002, ..., 366)

% U Week (0, 1, 52), where Sunday is the first day of the week

%u weeks (0, 1, 52), where Monday is the first day of the week

%m month name (January, February, ..., December)

%b Abbreviated month name (January, February, ..., December)

%m two digits of the month (01, 02, ..., 12)

The month (1, 2, ..., 12) that is represented by the%c number.

%Y four digits of year

%y two digits of year

Percent% direct value "%"