MySQL - CONVERT_TZ() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

UTC stands for Co-ordinated Universal Time. It is time standard and is commonly used across the world. The MYSQL CURDATE() is used to convert the given date from one time zone to another time zone. This function accepts 3 parameters −

• date − The date value you need to convert.

• from_tz − Current time zone of the date

• to_tz − Time zone to which you need to convert the current date.

Following is the list of various time zones –

Syntax

Following is the syntax of the above function –

CONVERT_TZ(dt,from_tz,to_tz)

Example 1

Following example demonstrates the usage of the CONVERT_TZ() function –

mysql> SELECT CONVERT_TZ('15:30:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2019-11-21 15:30:00','+00:00','+10:00') |
+-----------------------------------------------------+
|                                 2019-11-22 01:30:00 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT CONVERT_TZ('1919-06-21 21:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('1919-06-21 21:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
|                                 1919-06-21 21:00:00 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Example 3

Following query converts GMT(Greenwich Mean Time) to IST(Indian Standard time) –

mysql> SELECT CONVERT_TZ('1886-11-20 16:29:30', '+00:00','+05:30');
+------------------------------------------------------+
| CONVERT_TZ('1886-11-20 16:29:30', '+00:00','+05:30') |
+------------------------------------------------------+
|                                  1886-11-20 16:29:30 |
+------------------------------------------------------+
1 row in set (0.00 sec)

Example 4

Following query converts IST(Indian Standard Time) to GMT(Gulf standard Time)

mysql> SELECT CONVERT_TZ('1886-11-20 16:29:30', '+05:30','+04:00');
+------------------------------------------------------+
| CONVERT_TZ('1886-11-20 16:29:30', '+05:30','+04:00') |
+------------------------------------------------------+
|                                  1886-11-20 16:29:30 |
+------------------------------------------------------+
1 row in set (0.00 sec)

Example 5

Suppose we have created a table named dispatches_data with 5 records in it using the following queries –

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query converts the date in DispatchTimeStamp from column GMT(Greenwich Mean Time) to IST(Indian Standard time) —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, CONVERT_TZ(DispatchTimeStamp, '+00:00','+05:30') FROM dispatches_data;
+-------------+--------------+---------------------+-------+--------------------------------------------------+
| ProductName | CustomerName |   DispatchTimeStamp | Price | CONVERT_TZ(DispatchTimeStamp, '+00:00','+05:30') |
+-------------+--------------+---------------------+-------+--------------------------------------------------+
|   Key-Board |         Raja | 2019-05-04 15:02:45 |  7000 |                              2019-05-04 20:32:45 |
|   Earphones |         Roja | 2019-06-26 14:13:12 |  2000 |                              2019-06-26 19:43:12 |
|       Mouse |         Puja | 2019-12-07 07:50:37 |  3000 |                              2019-12-07 13:20:37 |
|      Mobile |       Vanaja | 2018-03-21 16:00:45 |  9000 |                              2018-03-21 21:30:45 |
|     Headset |       Jalaja | 2018-12-30 10:49:27 |  6000 |                              2018-12-30 16:19:27 |
+-------------+--------------+---------------------+-------+--------------------------------------------------+
5 rows in set (0.00 sec)

Example 6

Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberDetails (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimeStamp timestamp
);
insert into SubscriberDetails values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49'));
insert into SubscriberDetails values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19'));
insert into SubscriberDetails values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20'));
insert into SubscriberDetails values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39'));
insert into SubscriberDetails values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45'));

Following query converts the SubscriptionTimeStamp from GMT(Greenwich Mean Time) to PST(Pacific Standard Time) for all the records —

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp, CONVERT_TZ(SubscriptionTimeStamp, '+00:00','+08:00') FROM SubscriberDetails;
+----------------+-------------+-----------------------+------------------------------------------------------+
| SubscriberName | PackageName | SubscriptionTimeStamp | CONVERT_TZ(SubscriptionTimeStamp, '+00:00','+08:00') |
+----------------+-------------+-----------------------+------------------------------------------------------+
|           Raja |     Premium |   2020-10-21 20:53:49 |                                  2020-10-22 04:53:49 |
|           Roja |       Basic |   2020-11-26 10:13:19 |                                  2020-11-26 18:13:19 |
|           Puja |    Moderate |   2021-03-07 05:43:20 |                                  2021-03-07 13:43:20 |
|         Vanaja |       Basic |   2021-02-21 16:36:39 |                                  2021-02-22 00:36:39 |
|         Jalaja |     Premium |   2021-01-30 12:45:45 |                                  2021-01-30 20:45:45 |
+----------------+-------------+-----------------------+------------------------------------------------------+
5 rows in set (0.00 sec)