CODEWITHSUNDEEP
mysqldatetimetimezone
Tiny Dancer
Tiny Dancer

Reputation: 237

MySQL update datetime records from GMT+7 to UTC

In my database table there is a created_at column which has records in Y-m-d H:i:s GMT+7 format, i want to update all the created_at records into UTC format, for example:

/*Old Records in GMT+7 format*/
+-------------------+
|    created_at     |
+-------------------+
|2018-09-30 12:11:25|
|2018-10-01 02:44:05|
|2018-10-02 14:45:15|
+-------------------+


/*New Records in UTC format*/
+-------------------+
|    created_at     |
+-------------------+
|2018-09-30 05:11:25|
|2018-09-30 19:44:05|
|2018-10-02 07:45:15|
+-------------------+

Upvotes: 1

Views: 1297

Answers (1)

Fahmi
Fahmi

Reputation: 37483

use CONVERT_TZ(created_at,'+00:00','-7:00'):

demo

SELECT created_at,CONVERT_TZ(created_at,'+00:00','-7:00');

Output:

createddate          utctime
2018-10-02 08:56:29  2018-10-02 01:56:29

Upvotes: 2

Related Questions