CODEWITHSUNDEEP
mysqlsql-update
Christopher
Christopher

Reputation: 691

Issue of updating existing table with results of select statement for time zone conversion

I have a table called table1 with three columns, one of which is Date_Of_Call which is of datetime type with the data in PDT. I basically need to convert the data from PDT to UTC and put the UTC converted dates into a new column in the existing table. I added a new column with:

alter table table1 ADD Date_Of_Call_UTC DATETIME;

I am able to get the proper time conversion with this select statement:

select CONVERT_TZ(Date_Of_Call, '-7:00', '-0:00') from table1;

The issue I am having is trying to use an update command to take the results of the select statement and put them in the new Date_Of_Call_UTC column. Any thoughts of how to do this?

I tried the below statement and a few variations but can't quite figure out what I need to do:

update table1 set table1.Date_Of_Call_UTC = (select CONVERT_TZ(Date_Of_Call, '-7:00', '-0:00') from table1);

Any assistance is appreciated!

Upvotes: 0

Views: 32

Answers (1)

Iłya Bursov
Iłya Bursov

Reputation: 24156

this one should work:

update table1
set table1.Date_Of_Call_UTC = CONVERT_TZ(Date_Of_Call, '-7:00', '-0:00');

NOTE: dates are usually stored already as UTC in mysql, but during output they can be displayed with offset applied, read about it: http://dev.mysql.com/doc/refman/5.0/en/datetime.html and http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Upvotes: 1

Related Questions