10.7 MySQL Server Locale Support

The locale indicated by the lc_time_names system variable controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions.

lc_time_names does not affect the STR_TO_DATE() or GET_FORMAT() function.

The lc_time_names value does not affect the result from FORMAT(), but this function takes an optional third parameter that enables a locale to be specified to be used for the result number's decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable.

Locale names have language and region subtags listed by IANA (http://www.iana.org/assignments/language-subtag-registry) such as 'ja_JP' or 'pt_BR'. The default value is 'en_US' regardless of your system's locale setting, but you can set the value at server startup or set the GLOBAL value if you have the SUPER privilege. Any client can examine the value of lc_time_names or set its SESSION value to affect the locale for its own connection.

mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DAYNAME('2010-01-01'), MONTHNAME('2010-01-01');
+-----------------------+-------------------------+
| DAYNAME('2010-01-01') | MONTHNAME('2010-01-01') |
+-----------------------+-------------------------+
| Friday                | January                 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2010-01-01','%W %a %M %b');
+-----------------------------------------+
| DATE_FORMAT('2010-01-01','%W %a %M %b') |
+-----------------------------------------+
| Friday Fri January Jan                  |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SET lc_time_names = 'es_MX';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| es_MX           |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DAYNAME('2010-01-01'), MONTHNAME('2010-01-01');
+-----------------------+-------------------------+
| DAYNAME('2010-01-01') | MONTHNAME('2010-01-01') |
+-----------------------+-------------------------+
| viernes               | enero                   |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2010-01-01','%W %a %M %b');
+-----------------------------------------+
| DATE_FORMAT('2010-01-01','%W %a %M %b') |
+-----------------------------------------+
| viernes vie enero ene                   |
+-----------------------------------------+
1 row in set (0.00 sec)

The day or month name for each of the affected functions is converted from utf8 to the character set indicated by the character_set_connection system variable.

lc_time_names may be set to any of the following locale values. The set of locales supported by MySQL may differ from those supported by your operating system.

ar_AE: Arabic - United Arab Emiratesar_BH: Arabic - Bahrain
ar_DZ: Arabic - Algeriaar_EG: Arabic - Egypt
ar_IN: Arabic - Indiaar_IQ: Arabic - Iraq
ar_JO: Arabic - Jordanar_KW: Arabic - Kuwait
ar_LB: Arabic - Lebanonar_LY: Arabic - Libya
ar_MA: Arabic - Moroccoar_OM: Arabic - Oman
ar_QA: Arabic - Qatarar_SA: Arabic - Saudi Arabia
ar_SD: Arabic - Sudanar_SY: Arabic - Syria
ar_TN: Arabic - Tunisiaar_YE: Arabic - Yemen
be_BY: Belarusian - Belarusbg_BG: Bulgarian - Bulgaria
ca_ES: Catalan - Spaincs_CZ: Czech - Czech Republic
da_DK: Danish - Denmarkde_AT: German - Austria
de_BE: German - Belgiumde_CH: German - Switzerland
de_DE: German - Germanyde_LU: German - Luxembourg
el_GR: Greek - Greeceen_AU: English - Australia
en_CA: English - Canadaen_GB: English - United Kingdom
en_IN: English - Indiaen_NZ: English - New Zealand
en_PH: English - Philippinesen_US: English - United States
en_ZA: English - South Africaen_ZW: English - Zimbabwe
es_AR: Spanish - Argentinaes_BO: Spanish - Bolivia
es_CL: Spanish - Chilees_CO: Spanish - Columbia
es_CR: Spanish - Costa Ricaes_DO: Spanish - Dominican Republic
es_EC: Spanish - Ecuadores_ES: Spanish - Spain
es_GT: Spanish - Guatemalaes_HN: Spanish - Honduras
es_MX: Spanish - Mexicoes_NI: Spanish - Nicaragua
es_PA: Spanish - Panamaes_PE: Spanish - Peru
es_PR: Spanish - Puerto Ricoes_PY: Spanish - Paraguay
es_SV: Spanish - El Salvadores_US: Spanish - United States
es_UY: Spanish - Uruguayes_VE: Spanish - Venezuela
et_EE: Estonian - Estoniaeu_ES: Basque - Basque
fi_FI: Finnish - Finlandfo_FO: Faroese - Faroe Islands
fr_BE: French - Belgiumfr_CA: French - Canada
fr_CH: French - Switzerlandfr_FR: French - France
fr_LU: French - Luxembourggl_ES: Galician - Spain
gu_IN: Gujarati - Indiahe_IL: Hebrew - Israel
hi_IN: Hindi - Indiahr_HR: Croatian - Croatia
hu_HU: Hungarian - Hungaryid_ID: Indonesian - Indonesia
is_IS: Icelandic - Icelandit_CH: Italian - Switzerland
it_IT: Italian - Italyja_JP: Japanese - Japan
ko_KR: Korean - Republic of Korealt_LT: Lithuanian - Lithuania
lv_LV: Latvian - Latviamk_MK: Macedonian - FYROM
mn_MN: Mongolia - Mongolianms_MY: Malay - Malaysia
nb_NO: Norwegian(Bokmål) - Norwaynl_BE: Dutch - Belgium
nl_NL: Dutch - The Netherlandsno_NO: Norwegian - Norway
pl_PL: Polish - Polandpt_BR: Portugese - Brazil
pt_PT: Portugese - Portugalro_RO: Romanian - Romania
ru_RU: Russian - Russiaru_UA: Russian - Ukraine
sk_SK: Slovak - Slovakiasl_SI: Slovenian - Slovenia
sq_AL: Albanian - Albaniasr_RS: Serbian - Yugoslavia
sv_FI: Swedish - Finlandsv_SE: Swedish - Sweden
ta_IN: Tamil - Indiate_IN: Telugu - India
th_TH: Thai - Thailandtr_TR: Turkish - Turkey
uk_UA: Ukrainian - Ukraineur_PK: Urdu - Pakistan
vi_VN: Vietnamese - Viet Namzh_CN: Chinese - China
zh_HK: Chinese - Hong Kongzh_TW: Chinese - Taiwan Province of China