Your Location is: Home > Mysql

Trigger to convert date from timezone to another one

From: Tokyo View: 3464 Hend Mourad 
 mysql  triggers  mysql-workbench  datatrigger

Question

i want to create trigger to convert (s_start) value from (s_timezone) to (Africa/Cairo) Timezone and stored in new column (s_start_Cairo) for example.

enter image description here

Best answer

Firstly you need to capture the offset time between your two timezones. This is the query for that

SELECT TIME_FORMAT(TIMEDIFF(NOW(), CONVERT_TZ(NOW(), 'Europe/London', 'Africa/Cairo')), '%H:%i') AS offset;

Then you can add this offset to your start time and save the value in the new column

Tags