The MySQL CONVERT_TZ function is used to convert a datetime value from one time zone to another. This function is particularly useful when dealing with datetime data that needs to be presented or manipulated in a different time zone than the one in which it is stored.

Syntax

Here is the basic syntax of the CONVERT_TZ function:

CONVERT_TZ(datetime, from_tz, to_tz)

datetime: This is the datetime value that you want to convert.
from_tz: This is the original time zone of the datetime value.
to_tz: This is the target time zone to which you want to convert the datetime value.

Example

For example, let’s say you have a datetime value stored in the ‘UTC’ time zone, and you want to convert it to the ‘America/New_York’ time zone. You can use the CONVERT_TZ function like this:

SELECT CONVERT_TZ('2024-01-01 12:00:00', 'UTC', 'America/New_York');

This will return the datetime value adjusted for the time zone difference between UTC and America/New_York.

It’s important to note that the time zone values used in the CONVERT_TZ function should be valid time zone names. MySQL relies on the underlying time zone information available in the system, and it’s recommended to keep the time zone database up-to-date for accurate conversions.

Additionally, it’s crucial to be aware of daylight saving time changes, as the CONVERT_TZ function considers these changes when performing the conversion.

Here’s a brief example of how you might use this function in a query:

SELECT 
    event_name,
    CONVERT_TZ(event_start_time, 'UTC', 'America/Los_Angeles') AS start_time_pst
FROM 
    events;

In this example, assuming that the events table has columns event_name and event_start_time, the query retrieves the event names along with their start times converted from UTC to the ‘America/Los_Angeles’ time zone.