How to use the MySQL CONVERT_TZ() function

The CONVERT_TZ() function in MySQL converts a datetime value from one time zone to another. This allows converting datetimes between different timezones.

Syntax

The syntax for CONVERT_TZ() is:

CONVERT_TZ(datetime, from_tz, to_tz)

Where:

• datetime is the date/time value to convert
• from_tz is the original time zone
• to_tz is the target time zone

Examples

1. Convert datetime from Asia/Kolkata to America/New_York:

   SELECT CONVERT_TZ('2023-01-05 09:30:00', 'Asia/Kolkata', 'America/New_York');
   

   This converts the time from IST to EST timezone.

2. Convert datetime from UTC to Europe/London timezone:

   SELECT CONVERT_TZ('2023-03-12 13:30:00', '+00:00', 'Europe/London');
   

   This converts the UTC time to London local time.

3. Convert datetime from IST to PST timezone:

   SELECT CONVERT_TZ('2023-05-21 01:00:00', 'Asia/Kolkata', 'America/Los_Angeles');
   

   This converts 1 AM IST to PST time.

4. Convert datetime from Australia/Sydney to Asia/Tokyo:

   SELECT CONVERT_TZ('2023-09-14 08:00:00', 'Australia/Sydney', 'Asia/Tokyo');
   

   This converts the Sydney time to Tokyo local time.

5. Convert datetime from Europe/Berlin to America/Chicago:

   SELECT CONVERT_TZ('2023-11-10 14:30:00', 'Europe/Berlin', 'America/Chicago');
   

   This converts the Berlin time to Chicago time.

Other Similar Functions

• TIMESTAMP() - With time zone
• UNIX_TIMESTAMP() - Unix timestamp
• TIMEDIFF() - Time difference between two times
• UTC_TIMESTAMP() - Current UTC date and time

So CONVERT_TZ() allows converting datetimes between different time zones in MySQL.