TIMESTAMP WITH TIME ZONE - Oracle to MySQL Migration

Oracle provides TIMESTAMP WITH TIME ZONE data type that allows you to store datetime with the time zone information.

Oracle:

  -- Flights table stores departure time in airport time zone
  CREATE TABLE departures
  (
     airport CHAR(3),
     airline VARCHAR2(30),
     flight CHAR(7),
     departure TIMESTAMP WITH TIME ZONE
  );
 
  -- Departs from San Francisco at 5:25 pm local time
  INSERT INTO departures VALUES ('SFO', 'Virgin Atlantic', 'VS020', TIMESTAMP '2013-03-22 17:25:00 -8:00');
  -- Departs from London at 11:25 am local time
  INSERT INTO departures VALUES ('LHR', 'Singapore Airlines ', 'SQ2507', TIMESTAMP '2013-03-22 11:25:00 +0:00');

When an user searches flights in the specified airport, the departure time is shown in the airport local time, there is no need to perform any conversions:

  -- Show departure times for San Francisco International Airport
  SELECT * FROM departures WHERE airport = 'SFO';

Output in US/Pacific:

There is no a data type in MySQL that can store time zone information. DATETIME data type stores timestamps in the MySQL server time zone.

MySQL:

  -- Flights table will store departure time in UTC
  CREATE TABLE departures
  (
     airport CHAR(3),
     airline VARCHAR(30),
     flight CHAR(7),
     departure DATETIME
  );

When you transfer existing data with time zone information from Oracle to MySQL, you can use SYS_EXTRACT_UTC function in Oracle to get all timestamps in the UTC time zone:

Oracle:

  -- Convert timestampt to UTC on transfer
  SELECT airport, airline, flight, SYS_EXTRACT_UTC(departure) FROM departures;

Output in UTC:

Note. SQLines Data tool is capable of converting TIMESTAMP WITH TIME ZONE data to the UTC time zone in MySQL using SYS_EXTRACT_UTC function automatically.

Then depending on the application you can store time zone information in a separate column of the same table, or in a separate table.

In our example it makes sense to have a table that stores airport and its time zone as follows:

MySQL:

  -- Airport time zone information
  CREATE TABLE airport_timezone
  (
     airport CHAR(3),
     timezone VARCHAR(30)
  );
 
  INSERT INTO airport_timezone VALUES ('SFO', '-08:00');
  INSERT INTO airport_timezone VALUES ('LHR', '+00:00');

Now when an user searches flights in an airport you can use CONVERT_TZ function in MySQL to convert from UTC timezone to the time zone of the specified airport:

  -- Show departure times for San Francisco International Airport
  SELECT d.airport, airline, flight, CONVERT_TZ(departure, '+00:00', timezone)  
  FROM departures d, airport_timezone tz 
  WHERE d.airport = 'SFO' AND d.airport = tz.airport;

Output in US/Pacific time zone:

• Oracle to MySQL Migration Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.