How the GET_FORMAT() function works in Mariadb?

The GET_FORMAT() function is a date and time function that returns a date or time format string according to a given type and locale.

Posted on

The GET_FORMAT() function is a date and time function that returns a date or time format string according to a given type and locale. It can be used for formatting or parsing date or time values in different languages or regions.

Syntax

The syntax of the GET_FORMAT() function is as follows:

GET_FORMAT(type, locale)

The type parameter is a string that specifies the type of the format. It can be one of the following values:

  • ‘DATE’: Returns a date format string.
  • ‘DATETIME’: Returns a date and time format string.
  • ‘TIME’: Returns a time format string.

The locale parameter is a string that specifies the locale of the format. It can be one of the following values:

  • ‘EUR’: Returns a format string for the European locale.
  • ‘USA’: Returns a format string for the USA locale.
  • ‘JIS’: Returns a format string for the Japanese Industrial Standards locale.
  • ‘ISO’: Returns a format string for the ISO 9075 locale.
  • ‘INTERNAL’: Returns a format string for the internal representation of Mariadb.

The function returns a string that contains the format specifiers for the given type and locale. The format specifiers are the same as those used by the DATE_FORMAT() and TIME_FORMAT() functions.

Examples

Example 1: DATE type

The following example shows how to use the GET_FORMAT() function to return the date format string for different locales.

SELECT GET_FORMAT(DATE, 'EUR') AS eur_format,
       GET_FORMAT(DATE, 'USA') AS usa_format,
       GET_FORMAT(DATE, 'JIS') AS jis_format,
       GET_FORMAT(DATE, 'ISO') AS iso_format,
       GET_FORMAT(DATE, 'INTERNAL') AS internal_format;

The output is:

+------------+------------+------------+------------+-----------------+
| eur_format | usa_format | jis_format | iso_format | internal_format |
+------------+------------+------------+------------+-----------------+
| %d.%m.%Y   | %m.%d.%Y   | %Y-%m-%d   | %Y-%m-%d   | %Y%m%d          |
+------------+------------+------------+------------+-----------------+

The function returns the date format string for the European, USA, JIS, ISO, and internal locales. The format specifiers indicate the order and separator of the day, month, and year components.

Example 2: DATETIME type

The following example shows how to use the GET_FORMAT() function to return the date and time format string for different locales.

SELECT GET_FORMAT(DATETIME, 'EUR') AS eur_format,
       GET_FORMAT(DATETIME, 'USA') AS usa_format,
       GET_FORMAT(DATETIME, 'JIS') AS jis_format,
       GET_FORMAT(DATETIME, 'ISO') AS iso_format,
       GET_FORMAT(DATETIME, 'INTERNAL') AS internal_format;

The output is:

+-------------------+-------------------+-------------------+-------------------+-----------------+
| eur_format        | usa_format        | jis_format        | iso_format        | internal_format |
+-------------------+-------------------+-------------------+-------------------+-----------------+
| %Y-%m-%d %H.%i.%s | %Y-%m-%d %H.%i.%s | %Y-%m-%d %H:%i:%s | %Y-%m-%d %H:%i:%s | %Y%m%d%H%i%s    |
+-------------------+-------------------+-------------------+-------------------+-----------------+

The function returns the date and time format string for the European, USA, JIS, ISO, and internal locales. The format specifiers indicate the order and separator of the date and time components, as well as the hour, minute, and second components.

Example 3: TIME type

The following example shows how to use the GET_FORMAT() function to return the time format string for different locales.

SELECT GET_FORMAT(TIME, 'EUR') AS eur_format,
       GET_FORMAT(TIME, 'USA') AS usa_format,
       GET_FORMAT(TIME, 'JIS') AS jis_format,
       GET_FORMAT(TIME, 'ISO') AS iso_format,
       GET_FORMAT(TIME, 'INTERNAL') AS internal_format;

The output is:

+------------+-------------+------------+------------+-----------------+
| eur_format | usa_format  | jis_format | iso_format | internal_format |
+------------+-------------+------------+------------+-----------------+
| %H.%i.%s   | %h:%i:%s %p | %H:%i:%s   | %H:%i:%s   | %H%i%s          |
+------------+-------------+------------+------------+-----------------+

The function returns the time format string for the European, USA, JIS, ISO, and internal locales. The format specifiers indicate the hour, minute, and second components, as well as the optional AM/PM indicator for the USA locale.

Some of the functions that are related to the GET_FORMAT() function are:

  • DATE_FORMAT(): This function formats a date or datetime value according to a given format string. It can be used to convert a date or datetime value to a different format or locale.
  • TIME_FORMAT(): This function formats a time value according to a given format string. It can be used to convert a time value to a different format or locale.
  • STR_TO_DATE(): This function parses a date or time value from a given string according to a given format string. It can be used to convert a string to a date or time value in a different format or locale.

Conclusion

The GET_FORMAT() function is a useful function for returning a date or time format string according to a given type and locale. It can be used for formatting or parsing date or time values in different languages or regions. It can also be combined with other date and time functions to achieve various date and time operations.