--------------------------------------------------
-- Update UTC timestamps on issue and task header
-- Created 20110714
--------------------------------------------------

-- set utc field using timestamp from issue create event
UPDATE `cehior_auth_header`
  SET `utc` = (SELECT `timestamp` FROM `issue_event` WHERE `issue_id` = `unikid` AND `event_type` = 1);

-- set remaining values from issue header (this may not be correct timezone, but it's the best we can do)
UPDATE `cehior_auth_header`
  SET `utc` = TIMESTAMP(concat(
    substr(dato, 1, 4), '-', substr(dato, 5, 2), '-', substr(dato, 7, 2), ' ', 
    substr(tid, 1, 2), ':', substr(tid, 3, 2), ':', substr(tid, 5, 2)))
  WHERE `utc` IS NULL;
  
-- set utc create timestamps on task header
UPDATE `cehior_auth_header_task`
  SET `create_utc` = TIMESTAMP(`create_date`, `create_time`);
  
-- set utc finish timestamps on completed tasks
UPDATE `cehior_auth_header_task`
  SET `finish_utc` = TIMESTAMP(`finish_date`, `finish_time`)
  WHERE `finish_date` != '0000-00-00';
