Convert and Store Date and Time in other coloumn in MySQL

Question

I have database in MYSQL with ID, Req_Order_No(Varchar) , Req_In_Time(DateTime), Req_Out_Time(DateTime)

The Sample row is like below:

1   W0CH546  2014-07-23 09:32:00   2014-07-24 01:42:00

The above Date and Time are in EST format. I want to convert both of them and store in IST format in other columns

I tried SELECT CONVERT_TZ('Req_In_Time','-05:00','+9:30');

But it returns NULL Values.

Please help. Do I need php also?

Answer 1

The quotes around Req_In_Time cause the error.

SELECT CONVERT_TZ(Req_In_Time,'-05:00','+9:30');

Also, you should never store time information in localtime. Use UTC/GMT.

You can always convert it to the proper localtime when you display it.

Note: Of course you need to specify the table-name as well:

SELECT CONVERT_TZ(Req_In_Time,'-05:00','+9:30') FROM YOUR_TABLE_NAME;

So you add another column (e.g. column xxx) to YOUR_TABLE_NAME.
Then you update the values.

UPDATE YOUR_TABLE_NAME
     SET xxx = CONVERT_TZ(Req_In_Time,'-05:00','+9:30') 

BTW, to add the column:

ALTER TABLE YOUR_TABLE_NAME ADD COLUMN `xxx` datetime NULL ;