How the CONVERT_TZ() function works in Mariadb?

The MariaDB CONVERT_TZ() function is used to convert a datetime value from one time zone to another. This is particularly useful in applications that support users across different time zones and need to display datetime values in a localized format.

Syntax

The syntax for the MariaDB CONVERT_TZ() function is as follows:

CONVERT_TZ(dt, from_tz, to_tz)

Where dt is the datetime value to convert, from_tz is the time zone to convert from, and to_tz is the time zone to convert to. The function returns the converted datetime value.

Examples

Example 1: Basic Conversion

This example demonstrates how to convert a datetime from UTC to Pacific Standard Time (PST).

SELECT CONVERT_TZ('2024-03-16 20:18:05', '+00:00', '-08:00');

The output for this statement is:

'2024-03-16 12:18:05'

This shows the datetime adjusted from UTC to 8 hours behind, which is PST.

Example 2: Using Named Time Zones

This example shows the conversion using named time zones instead of offsets.

SELECT CONVERT_TZ('2024-03-16 20:18:05', 'GMT', 'America/Los_Angeles');

The output for this statement is:

'2024-03-16 12:18:05'

This also converts the time from GMT to the time in Los Angeles.

Example 3: Conversion Between Non-UTC Time Zones

Here we convert a datetime from Tokyo Time (JST) to Central European Time (CET).

SELECT CONVERT_TZ('2024-03-16 20:18:05', 'Asia/Tokyo', 'Europe/Paris');

The output for this statement is:

'2024-03-16 12:18:05'

The datetime has been adjusted to reflect the time difference between Tokyo and Paris.

Example 4: Handling Daylight Saving Time

This example takes into account daylight saving time when converting from EST to EDT.

SELECT CONVERT_TZ('2024-11-02 20:18:05', 'EST', 'EDT');

The output for this statement is:

'2024-11-02 21:18:05'

The time has been adjusted forward by one hour to account for daylight saving time.

Example 5: Invalid Time Zone

This example attempts to convert a datetime using an invalid time zone.

SELECT CONVERT_TZ('2024-03-16 20:18:05', 'GMT', 'Mars/SpaceStation');

The output for this statement is:

NULL

Since ‘Mars/SpaceStation’ is not a valid time zone, the function returns NULL.

Related Functions

Below are a few functions related to the MariaDB CONVERT_TZ() function:

• MariaDB NOW() function is used to get the current date and time.
• MariaDB SYSDATE() function is used to get the current date and time, similar to NOW(), but returns the time at which it executes.
• MariaDB TIMEDIFF() function is used to calculate the difference between two time values.
• MariaDB DATE_ADD() and DATE_SUB() functions are used to add or subtract a specified time interval from a date.

Conclusion

Understanding the CONVERT_TZ() function in MariaDB is essential for managing datetime values across different time zones. By using this function, developers can ensure that their applications display the correct local time for users around the world. Remember to always check for valid time zone strings to avoid unexpected NULL results.