Time Zone¶

Time zone affects some data types (e.g., Timestamp and Time) and operations (e.g., to_char). Tables can have different time zones. Internally, Tajo translates all table rows to UTC values and processes them. It becomes easier for Tajo to handle multiple different time zones.

In Tajo, there are some time zong settings.

Server Cluster Time Zone¶

One Tajo cluster has a system time zone which globally affects all tables in which the table property ‘time zone’ are not specified.

You can set the system time zone in conf/tajo-site.xml file as follows:

tajo-site.xml

<name>tajo.timezone</name>
<value>GMT+9</value>

Table Time Zone¶

In Tajo, a table property timezone allows users to specify a time zone that the table uses for reading or writing. When each table row are read or written, `timestamp` and `time` column values are adjusted by a given time zone if it is set.

You can specify a table time zone as follows:

CREATE EXTERNAL TABLE table1 (
 t_timestamp  TIMESTAMP,
 t_date    DATE
) USING TEXT WITH('timezone'='ASIA/Seoul') LOCATION '/path-to-table/'

In order to learn table properties, please refer to Overview of Tajo Tables.

Client Time Zone¶

Each client has its own time zone setting. It translates retrieved timestamp and time values by time zone. In order to set client time zone, you should set the session variable TIMEZONE. There are some ways to set this session variable.

In tsql, you can use \set timezone meta command as follows:

tsql

default> \set timezone GMT+9

The following ways use SQL statements. So, this way is available in tsql, JDBC, and Tajo Java API.

SQL

SET TIME ZONE 'GMT+9';

or

SET SESSION TIMEZONE TO 'GMT+9';

Time Zone ID¶

Time zone can be an abbreviation form like ‘PST’ or ‘DST’. Also, it accepts an offset-based form like ‘GMT+9’ or UTC+9’ or a location-based form like ‘Asia/Seoul’.

Note

In many cases, offset-based forms or locaion-based forms are recommanded. In order to know the list of time zones, please refer to List of tz database time zones

Note

Java 6 does not recognize many location-based time zones and an offset-based timezone using the prefix ‘UTC’. We highly recommanded using the offset-based time zone using the prefix ‘GMT’. In other words, you should use ‘GMT-7’ instead of ‘UTC-7’ in Java 6.

Examples of Time Zone¶

For example, consider that there is a list of delimited text lines where each rows are written with Asia/Seoul time zone (i.e., GMT + 9).

1980-4-1 01:50:30.010|1980-04-01
80/4/1 1:50:30 AM|80/4/1
1980 April 1 1:50:30|1980-04-01

In order to register the table, we should put a table property 'timezone'='Asia/Seoul' in CREATE TABLE statement as follows:

CREATE EXTERNAL TABLE table1 (
 t_timestamp  TIMESTAMP,
 t_date    DATE
) USING TEXT WITH('text.delimiter'='|', 'timezone'='ASIA/Seoul') LOCATION '/path-to-table/'

By default, tsql and TajoClient API use UTC time zone. So, timestamp values in the result are adjusted by the time zone offset. But, date is not adjusted because date type does not consider time zone.

default> SELECT * FROM table1
t_timestamp,            t_date
----------------------------------
1980-03-31 16:50:30.01, 1980-04-01
1980-03-31 16:50:30   , 1980-04-01
1980-03-31 16:50:30   , 1980-04-01

In addition, users can set client-side time zone by setting a session variable ‘TZ’. It enables a client to translate timestamp or time values to user’s time zoned ones.

default> SET TIME ZONE 'Asia/Seoul'
default> SELECT * FROM table1
t_timestamp,            t_date
----------------------------------
1980-04-01 01:50:30.01, 1980-04-01
1980-04-01 01:50:30   , 1980-04-01
1980-04-01 01:50:30   , 1980-04-01