This topic describes how to use a SET command to configure the time zone for a MaxCompute project.
The following products support the time zone configuration feature:
- MaxCompute MapReduce
- MaxCompute Spark
- If tasks are submitted to the MaxCompute computing cluster, the time zone of the project is automatically obtained.
- If tasks are submitted from spark-shell, spark-sql, or pyspark in yarn-client mode, you must configure parameters in the spark-defaults.conf file of the driver and add
spark.driver.extraJavaOptions -Duser.timezone=America/Los_Angeles. The timezone parameter indicates the time zone you want to use.
- Machine Learning Platform for AI
- MaxCompute Graph
By default, the time zone of a MaxCompute project is UTC+8. The DATETIME, TIMESTAMP, and DATE fields and the related built-in time functions are all calculated based on UTC+8. You can use one of the following methods to configure the time zone:
- Session level: Submit the
set odps.sql.timezone=<timezoneid>;statement along with a computing statement for execution. An example is as follows:
--Set the time zone to Asia/Tokyo. set odps.sql.timezone=Asia/Tokyo; --Query the current time zone. select getdate(); output: +------------+ | _c0 | +------------+ | 2018-10-30 23:49:50 | +------------+
- Project level: Execute the
setProject odps.sql.timezone=<timezoneid>;statement. Only the project owner has the permission to execute this statement.Notice Once the time zone of a project is set, it is used for all time computing, and the data of existing tasks is affected. Therefore, set the time zone only when it is absolutely necessary. We recommend that you only set time zones for new projects.
Limits and precautions
- SQL built-in date functions, UDFs, UDTs, UDJs, and the
SELECT TRANSFORMstatement allow you to obtain the timezone attribute of a project to set the time zone.
- A time zone must be configured in the format such as
Asia/Shanghai, which supports daylight saving time. Do not configure it in the GMT+9 format.
- If the time zone in SDK differs from that of the project, you must configure the GMT time zone to convert the data type from DATETIME to STRING.
- After the time zone is configured, there are differences between the real time and the output time of related SQL statements you run in DataWorks. Between the years of 1900 and 1928, the time difference is 352 seconds. Before the year of 1900, the time difference is 9 seconds.
- MaxCompute, Java SDK, and the related client are upgraded to ensure that DATETIME data stored in MaxCompute is correct across multiple time zones. The target Java SDK and client versions have the -oversea suffix. The upgrade may affect the display of DATETIME data earlier than January 1, 1928 in MaxCompute.
- If the local time zone is not UTC+8 when you upgrade MaxCompute, we recommend that you upgrade the Java SDK and related client. This ensures that the SQL-based computing results and data transferred by using the Tunnel command after January 1, 1900 are accurate and consistent. For DATETIME data earlier than January 1, 1900, the SQL-based computing results and data transferred by using the Tunnel command might differ up to 343 seconds. For DATETIME data earlier than January 1, 1928 uploaded before the Java SDK and the related client are upgraded, the time in the new versions is 352 seconds earlier.
- If you do not upgrade the Java SDK and client to versions with the -oversea suffix, the SQL-based computing results differ from data transferred by using the Tunnel command. For data earlier than January 1, 1900, the time difference is 9 seconds. For data between January 1, 1900 and January 1, 1928, the time difference is 352 seconds.
Note Modifying the time zone configuration in the Java SDK or client does not affect the time zone configuration in DataWorks. Therefore, the time zones are different. You need to evaluate how this may impact scheduled tasks in DataWorks. The time zone of a DataWorks server in Japan is GMT+9, and that in Singapore is GMT+8.
- If you are using a third-party client that is connected to MaxCompute by using Java Database Connectivity, you must set the time zone on the client to ensure that the time of the client and that of the server are consistent.