DBMS – Page 360

How to Set the Locale for the Current Connection in MySQL

February 14, 2020June 29, 2018 by Ian

There are certain date functions in MySQL that the return a day name or month name. In particular, I’m referring to the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions. These might return a value of say, November, or Monday, depending on the query being used. But the results could just as easily be returned in a different language if required.

The language that these functions use for their return value is derived from the lc_time_names system variable. You can view the value of this variable or set its SESSION value so that the results of these functions is in the desired language/locale.

Locale names have language and region subtags listed by the Internet Assigned Numbers Authority (IANA). Examples include en_US for English – United States, en_NZ for English – New Zealand, or es_PA for Spanish – Panama, etc (for a list of locales supported by MySQL, see Full List of Locales in MySQL).

In this article, I’ll show you how to find the current locale for your connection, change it, and then see how it affects the results of a query. I also show you a function that’s immune to this setting (but don’t worry, this function allows you to specify the locale).

PERIOD_DIFF() Examples – MySQL

January 28, 2021June 29, 2018 by Ian

MySQL has a PERIOD_DIFF() function that enables you to find the difference between two periods. The periods are provided as two separate arguments, and they should in the format YYMM or YYYYMM.

PERIOD_ADD() Examples – MySQL

January 28, 2021June 29, 2018 by Ian

MySQL has a PERIOD_ADD() function that allows you to add a number of months to a given period. It returns a value in the format YYYYMM.

This article provides examples to demonstrate how it works.

MONTHNAME() Examples – MySQL

October 17, 2021June 28, 2018 by Ian

In MySQL, the MONTHNAME() function returns the month name from a date.

For example, if you provide a date of 2021-12-07, the MONTHNAME() function will return December.

MONTH() Examples – MySQL

January 28, 2021June 28, 2018 by Ian

You can use the MONTH() function in MySQL to return the month from a date.

In this context, the month is a value between 1 and 12 (or 0 for dates with a zero month part).

For example, if you provide a date of 2018-10-07, the MONTH() function will return 10.

MAKETIME() Examples – MySQL

January 28, 2021June 28, 2018 by Ian

When using MySQL, you can use the MAKETIME() function to return a time from the various time parts.

In other words, you provide three arguments; the hour, the minutes, and the seconds. The MAKETIME() function will then return the time value based on those two arguments.

MAKEDATE() Examples – MySQL

January 28, 2021June 28, 2018 by Ian

When using MySQL, you can use the MAKEDATE() function to return a date from the year and day-of-year parts.

In other words, you provide two arguments; one being the year, and the other being the day-of-year. The MAKEDATE() function will then return the date value based on those two arguments.

LOCALTIME Examples – MySQL

January 28, 2021June 27, 2018 by Ian

The MySQL LOCALTIME function is a synonym for the NOW() function. It returns the current date and time.

The value is returned in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

LOCALTIMESTAMP Examples – MySQL

January 28, 2021June 27, 2018 by Ian

The MySQL LOCALTIMESTAMP function is a synonym for the NOW() function. It returns the current date and time.

The value is returned in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

LAST_DAY() Examples – MySQL

January 28, 2021June 27, 2018 by Ian

When using MySQL, you can use the LAST_DAY() function to return the last day of a given month.

You can provide the current date, or you can provide another specified date (for example, a date from a column in a database), and LAST_DAY() will return the last day of the month for that date.