Set Time Zones in Projects to Ensure Consistent Query Results - MaxCompute

Configure the time zone for a MaxCompute project using the SET command. By default, MaxCompute uses UTC+8 for all DATETIME, TIMESTAMP, and DATE fields and their related built-in time functions.

Supported job types

Time zone configuration applies to the following job types:

MapReduce
Spark on MaxCompute
- Tasks submitted to the MaxCompute computing cluster automatically inherit the project time zone.
- Tasks running in YARN client mode (spark-shell, spark-sql, or pyspark) require additional driver configuration. In spark-defaults.conf, add spark.driver.extraJavaOptions -Duser.timezone=America/Los_Angeles, replacing America/Los_Angeles with the time zone you want to use.
Machine Learning Platform for AI (PAI)
Graph

Configure the time zone

MaxCompute supports two configuration scopes:

Scope	Who can set it
Session level	Any user
Project level	Project owner only

Set the time zone for a session

Submit SET odps.sql.timezone=<timezoneid>; together with your query statement.

-- Set the time zone to Asia/Tokyo for this session.
SET odps.sql.timezone=Asia/Tokyo;

To verify the active time zone, run SELECT getdate();:

SELECT getdate();

Expected output:

+---------------------+
| _c0                 |
+---------------------+
| 2018-10-30 23:49:50 |
+---------------------+

Set the time zone for a project

Run the following command as the project owner:

setProject odps.sql.timezone=<timezoneid>;

Important

After you change the project time zone, all time computations in the project use the new time zone, including data from existing jobs. To minimize impact, configure the project time zone only when necessary, and prefer setting it on new projects rather than active ones.

Limitations and usage notes

Time zone format

Use IANA time zone names such as Asia/Shanghai. Do not use the GMT+9 offset format.

The GMT+N format does not support daylight saving time, which can cause incorrect time calculations. IANA time zone names handle daylight saving time automatically and avoid ambiguity.

SDK and client compatibility

SQL built-in date functions, user-defined functions (UDFs), user-defined types (UDTs), user-defined joins (UDJs), and the SELECT TRANSFORM statement can all read the project time zone attribute.
If the time zone configured in SDK for Java differs from the project time zone, configure the GMT time zone in the SDK to correctly convert DATETIME to STRING.
If your local time zone is not UTC+8 when updating MaxCompute, update SDK for Java and the related client to the versions with the -oversea suffix. This ensures that SQL results and data transferred using Tunnel commands dated after January 1, 1900 are accurate and consistent.

Historical datetime differences

After configuring a non-UTC+8 time zone, differences may exist between the actual time and the output of SQL statements run in DataWorks:

Date range	Time difference
Before January 1, 1900	9 seconds
January 1, 1900 – January 1, 1928	352 seconds

After updating to the `-oversea` suffix versions:

DATETIME data before January 1, 1900 may still differ by up to 343 seconds.
DATETIME data before January 1, 1928 that was uploaded before the update will appear 352 seconds earlier in the new versions.

Without the `-oversea` suffix update:

SQL results may differ from data transferred by Tunnel commands.
Before January 1, 1900: 9-second difference.
January 1, 1900 to January 1, 1928: 352-second difference.

DataWorks time zone

Changing the SDK for Java or client time zone does not affect the DataWorks server time zone. Evaluate how this affects your scheduled DataWorks jobs. DataWorks server time zones by region:

Region	Time zone
Japan (Tokyo)	GMT+9
Singapore (Singapore)	GMT+8

Third-party JDBC clients

If you connect to MaxCompute using a third-party client via Java Database Connectivity (JDBC), configure the matching time zone on the client to maintain consistency with the server.