CODEWITHSUNDEEP
mysqlsql
user5917011
user5917011

Reputation: 1137

Updating Time Zone for a Date field

I have a MySQL table that has date field in UTC default format. I want to also store the same date/time in PST format in another column in same table. Is it possible to do it in an SQL?

Upvotes: 0

Views: 227

Answers (2)

Dylan Su
Dylan Su

Reputation: 6065

You can use convert_tz to do conversion between different time zone.

mysql> select convert_tz('2016-03-16 7:00:00', '+00:00','-08:00');
+-----------------------------------------------------+
| convert_tz('2016-03-16 7:00:00', '+00:00','-08:00') |
+-----------------------------------------------------+
| 2016-03-15 23:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

To do the task you mentioned in your post, just use a UPDATE as below

update tbl set col2 = convert_tz(col1, '+00:00','-08:00');

Upvotes: 1

user5917011
user5917011

Reputation: 1137

I guess I figured it out. Using DATE_SUB(datetimeutc,INTERVAL 8 HOUR) resolved my problem.

Upvotes: 0

Related Questions