MySQL CONVERT_TZ() function

CONVERT_TZ() function

In MySQL the CONVERT_TZ() returns a resulting value after converting a datetime value from a time zone specified as the second argument to the time zone specified as the third argument. This function returns NULL when the arguments are invalid. It's particularly useful when dealing with datetime values that are stored in one time zone but need to be presented or analyzed in a different time zone.

This function is useful in -

• This is essential when dealing with international or multi-time zone data.
• The CONVERT_TZ() function accounts for daylight saving time changes, which can have a significant impact on datetime conversions.
• When scheduling events or appointments across different time zones, the function ensures accurate representation of datetime values for all parties involved.
• Time zone conversion is crucial when analyzing data that is collected or recorded in different parts of the world, allowing for meaningful insights and comparisons.
• For applications that involve real-time data updates or notifications, the function ensures that datetime values are displayed correctly based on the user's time zone.
• When migrating data from one database system to another with different default time zones, the function helps preserve the correct time zone information.
• In communication systems that involve datetime stamps, such as chat applications or email clients, the CONVERT_TZ() function ensures proper representation of time across time zones.

Syntax:

CONVERT_TZ (dt, from_tz,to_tz)

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL CONVERT_TZ() function

The following statement will convert the datetime value 2008-05-15 12:00:00 from +00:00 timezone to +10:00 timezone.

Code:

SELECT CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00');

Output:

mysql> SELECT CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2008-05-15 22:00:00                                 | 
+-----------------------------------------------------+
1 row in set (0.02 sec)

Video Presentation:

All Date and Time Functions :

Click here to see the MySQL Date and time functions.

Previous: ADDTIME()
Next: CURDATE()