Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle TIMEZONE data type and functions and MySQL CONVERT_TZ function

With Amazon DMS, you can convert date and time values between different time zones when migrating databases. The Oracle TIMEZONE data type and functions, along with the MySQL CONVERT_TZ function, facilitate working with timestamps across time zones. The following sections provide details on leveraging Oracle TIMEZONE and MySQL CONVERT_TZ during database migrations using Amazon DMS.

Oracle usage

Oracle uses data types and functions to integrate with time zones. For more information, see A Time Zones in the Oracle documentation.

The following data types are variants of TIMESTAMP:

Best practices:

Time zone functions

Examples

Create a table using TIMESTAMP WITH LOCAL TIME ZONE. Note that the last inserted row is displayed as a local session timestamp. It is the only row inserted using a specific time zone that is not LOCAL.

CREATE TABLE tz_local
(id NUMBER, tz_col TIMESTAMP WITH LOCAL TIME ZONE);

INSERT INTO tz_local VALUES(1, '01-JAN-2018 2:00:00');
INSERT INTO tz_local VALUES(2, TIMESTAMP '2018-01-01 2:00:00');
INSERT INTO tz_local VALUES(3, TIMESTAMP '2018-01-01 2:00:00 -08:00');

COMMIT;

SELECT * FROM tz_local;

ID  TZ_COL
1   2018-01-01 02:00:00
2   2018-01-01 02:00:00
3   2018-01-01 05:00:00

Create a table using TIMESTAMP WITH TIME ZONE. Note that the last inserted row is displayed as a local session timestamp. It is the only row that inserted using a specific time zone.

ALTER SESSION SET TIME_ZONE='-4:00';
CREATE TABLE tz_tbl (id NUMBER, tz_col TIMESTAMP WITH TIME ZONE);

INSERT INTO tz_tbl VALUES(1, '01-JAN-2018 2:00:00 AM -5:00');
INSERT INTO tz_tbl VALUES(2, TIMESTAMP '2018-01-01 3:00:00');
INSERT INTO tz_tbl VALUES(3, TIMESTAMP '2018-01-01 2:00:00 -8:00');

COMMIT;

SELECT * FROM tz_tbl;
ID  TZ_COL
1   01-JAN-03 02:00.00:000000 AM -07:00
2   01-JAN-03 02:00:00.000000 AM -07:00
3   01-JAN-03 02:00:00.000000 AM -08:00

MySQL usage

MySQL uses time zone data type and functions similar to Oracle. Unlike Oracle, MySQL does not have many time zone options. Most functionality can be achieved when querying and not when running DDLs such as CREATE TABLE command in Oracle.

When the server starts, it places the host time zone in the system_time_zone system variable. This variable can be modified by setting the time zone operating system environment variable.

There is no equivalent option for Oracle CREATE TABLE…​TIMESTAMP WITH TIME ZONE.

Comparison of time zone functions

Examples

Query the global and session level time zone.

SELECT @@global.time_zone, @@session.time_zone;

@@global.time_zone  @@session.time_zone
SYSTEM              Europe/Moscow

For more information, see MySQL Server Time Zone Support and Date and Time Functions in the MySQL documentation.