This topic describes how to set the time zone for a job in Realtime Compute.

Note This topic applies to Realtime Compute V1.6.0 and later.

Introduction

Realtime Compute allows you to set the time zone for an entire job. The default time zone is UTC+8. Examples of valid time zones are Asia/Shanghai, America/New_York, and UTC. For the list of supported time zones, see the last part of this topic.

You can also set the time zone for a source or sink table independently. Assume that you want to read data from or write data into a MySQL database where the Time, Date, and Timestamp columns use the America/New_York time zone. However, the Asia/Shanghai time zone needs to be used in the computation of a job. In this scenario, you can set the time zone for a source or sink table independently as follows:
CREATE TABLE mysql_source_my_table (
 -- ... 
) WITH ( 
timeZone='America/New_York'
 -- ... 
)

Examples

In Realtime Compute V1.6.0 and later, all time zone-related functions compute data based on custom time zones in terms of semantics. The following uses the custom time zone Asia/Shanghai as an example to describe the functions:
  • Functions for converting a string to a timestamp (TO_TIMESTAMP, TIMESTAMP, and UNIX_TIMESTAMP)
    -- Scalar function 
    TO_TIMESTAMP("2018-03-14 19:01:02.123") 
    -- SQL Literal 
    
    TIMESTAMP '2018-03-14 19:01:02.123' 
    -- Output: 
    -- Realtime Compute V1.6.0 and later: `1521025262123`.
    -- Realtime Compute V1.5.x: `1520996462123`.
    -- The UNIX_TIMESTAMP function can be used for a similar purpose. The difference lies in that its output is measured in seconds.
  • Functions for converting a timestamp to a string (FROM_UNIXTIME and DATE_FORMAT)
    Note If the input parameter is of the TIMESTAMP type, the output depends on your custom time zone.
    SELECT DATE_FORMAT(TO_TIMESTAMP(1520960523000), 'yyyy-MM-dd HH:mm:ss') 
    -- Output: 
    -- Realtime Compute V1.6.0 and later: `2018-03-14 01:02:03`.
    -- Realtime Compute V1.5.x: `2018-03-13 15:02:03`.
    
    SELECT DATE_FORMAT(TO_TIMESTAMP(1520960523000), 'yyyy-MM-dd HH:mm:ss') 
    -- Output: 
    -- Realtime Compute V1.6.0 and later: `2018-03-14 01:02:03`.
    -- Realtime Compute V1.5.x: `2018-03-13 15:02:03`.
    
    
    -- Note that in the following example, the output in Realtime Compute V1.6.0 is consistent with that in Realtime Compute V1.5.x. This is because the input and output time strings are computed based on the same time zone.
    
    DATE_FORMAT('2018-03-14 01:02:03', 'yyyy-MM-dd HH:mm:ss', 'yyyy/MM/dd HH:mm:ss') 
    FROM_UNIXTIME(1521025200000/1000) 
    -- Output: 
    -- Realtime Compute V1.6.0 and later: `2018-03-14 19:00:00`.
    -- Realtime Compute V1.5.x: `2018-03-14 11:00:00`.
    
  • Time-related computation functions
    If the input parameter is of the TIMESTAMP type, the output of the EXTRACT, FLOOR, CEIL, or DATEDIFF function depends on your custom time zone. If the input parameter is a string, the output in Realtime Compute V1.6.0 is consistent with that in Realtime Compute V1.5.x. This is because the input and output time strings are computed based on the same time zone.
    -- 1521503999000 2018-03-19T23:59:59+0000, 2018-03-20T07:59:59+0800
     EXTRACT(DAY FROM TO_TIMESTAMP(1521503999000)) 
    -- Output: 
    -- Realtime Compute V1.6.0 and later: `20`, which indicates the 20th day of the month in UTC+8.
    -- Realtime Compute V1.5.x: `19`.
    
  • Functions for computing the current time (LOCALTIMESTAMP(), CURRENT_TIMESTAMP(), NOW(), and UNIX_TIMESTAMP())
    In Realtime Compute V1.6.0, the semantics of the LOCALTIMESTAMP function are changed to return the current timestamp. In contrast, the DATE_FORMAT function does not involve a time zone in Realtime Compute V1.5.x. To make sure that the output of DATE_FORMAT(CURRENT_TIMESTAMP) is correct, the default time zone offset is added to the LOCALTIMESTAMP function, which is incorrect.
    -- The current time is 2018-04-03 16:56:10 in the Asia/Shanghai time zone. 
    SELECT DATE_FORMAT(CURRENT_TIMESTAMP, ‘yyyy-MM-dd HH:mm:ss’); 
    -- Output: 
    -- Realtime Compute V1.6.0 and later: `2018-04-03 16:56:10`. 
    -- Realtime Compute V1.5.x: `2018-04-03 08:56:10`. 
    
    SELECT DATE_FORMAT(LOCALTIMESTAMP, ‘yyyy-MM-dd HH:mm:ss’); 
    -- Output: 
    -- Realtime Compute V1.6.0: `2018-04-03 16:56:10`. 
    -- Realtime Compute V1.5.x: `2018-04-03 16:56:10`. 
    -- The same output is returned in Realtime Compute V1.6.0 and V1.5.x. However, the output timestamps of the LOCALTIMESTAMP function are actually different in these versions. 
    SELECT FROM_UNIXTIME(NOW()); SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); 
    -- Output: 
    -- Realtime Compute V1.6.0 and later: `2018-04-03 16:56:10`. 
    -- Realtime Compute V1.5.x: `2018-04-03 08:56:10`. 
    -- The semantics of the NOW() and UNIX_TIMESTAMP() functions remain unchanged in Realtime Compute V1.6.0 and V1.5.x to return the current timestamp, in seconds. The output results are different because the time zone is considered in the semantics of the FROM_UNIXTIME function in Realtime Compute V1.6.0, but not in earlier versions.
  • Date and Time functions

    The date and time data is expressed and computed as integers within Flink SQL. Date refers to the number of days that have elapsed after 00:00:00 Thursday, 1 January 1970. Time refers to the number of milliseconds that have elapsed after 00:00:00 in the current day of your time zone. If you compute the date and time data in UDFs, note that a time zone offset has been added to the Java object when the internal data is converted to the java.sql.Date and java.sql.Time types.

List of supported time zones

  • Africa/Abidjan
  • Africa/Accra
  • Africa/Addis_Ababa
  • Africa/Algiers
  • Africa/Asmara
  • Africa/Asmera
  • Africa/Bamako
  • Africa/Bangui
  • Africa/Banjul
  • Africa/Bissau
  • Africa/Blantyre
  • Africa/Brazzaville
  • Africa/Bujumbura
  • Africa/Cairo
  • Africa/Casablanca
  • Africa/Ceuta
  • Africa/Conakry
  • Africa/Dakar
  • Africa/Dar_es_Salaam
  • Africa/Djibouti
  • Africa/Douala
  • Africa/El_Aaiun
  • Africa/Freetown
  • Africa/Gaborone
  • Africa/Harare
  • Africa/Johannesburg
  • Africa/Juba
  • Africa/Kampala
  • Africa/Khartoum
  • Africa/Kigali
  • Africa/Kinshasa
  • Africa/Lagos
  • Africa/Libreville
  • Africa/Lome
  • Africa/Luanda
  • Africa/Lubumbashi
  • Africa/Lusaka
  • Africa/Malabo
  • Africa/Maputo
  • Africa/Maseru
  • Africa/Mbabane
  • Africa/Mogadishu
  • Africa/Monrovia
  • Africa/Nairobi
  • Africa/Ndjamena
  • Africa/Niamey
  • Africa/Nouakchott
  • Africa/Ouagadougou
  • Africa/Porto-Novo
  • Africa/Sao_Tome
  • Africa/Timbuktu
  • Africa/Tripoli
  • Africa/Tunis
  • Africa/Windhoek
  • America/Adak
  • America/Anchorage
  • America/Anguilla
  • America/Antigua
  • America/Araguaina
  • America/Argentina/Buenos_Aires
  • America/Argentina/Catamarca
  • America/Argentina/ComodRivadavia
  • America/Argentina/Cordoba
  • America/Argentina/Jujuy
  • America/Argentina/La_Rioja
  • America/Argentina/Mendoza
  • America/Argentina/Rio_Gallegos
  • America/Argentina/Salta
  • America/Argentina/San_Juan
  • America/Argentina/San_Luis
  • America/Argentina/Tucuman
  • America/Argentina/Ushuaia
  • America/Aruba
  • America/Asuncion
  • America/Atikokan
  • America/Atka
  • America/Bahia
  • America/Bahia_Banderas
  • America/Barbados
  • America/Belem
  • America/Belize
  • America/Blanc-Sablon
  • America/Boa_Vista
  • America/Bogota
  • America/Boise
  • America/Buenos_Aires
  • America/Cambridge_Bay
  • America/Campo_Grande
  • America/Cancun
  • America/Caracas
  • America/Catamarca
  • America/Cayenne
  • America/Cayman
  • America/Chicago
  • America/Chihuahua
  • America/Coral_Harbour
  • America/Cordoba
  • America/Costa_Rica
  • America/Creston
  • America/Cuiaba
  • America/Curacao
  • America/Danmarkshavn
  • America/Dawson
  • America/Dawson_Creek
  • America/Denver
  • America/Detroit
  • America/Dominica
  • America/Edmonton
  • America/Eirunepe
  • America/El_Salvador
  • America/Ensenada
  • America/Fort_Nelson
  • America/Fort_Wayne
  • America/Fortaleza
  • America/Glace_Bay
  • America/Godthab
  • America/Goose_Bay
  • America/Grand_Turk
  • America/Grenada
  • America/Guadeloupe
  • America/Guatemala
  • America/Guayaquil
  • America/Guyana
  • America/Halifax
  • America/Havana
  • America/Hermosillo
  • America/Indiana/Indianapolis
  • America/Indiana/Knox
  • America/Indiana/Marengo
  • America/Indiana/Petersburg
  • America/Indiana/Tell_City
  • America/Indiana/Vevay
  • America/Indiana/Vincennes
  • America/Indiana/Winamac
  • America/Indianapolis
  • America/Inuvik
  • America/Iqaluit
  • America/Jamaica
  • America/Jujuy
  • America/Juneau
  • America/Kentucky/Louisville
  • America/Kentucky/Monticello
  • America/Knox_IN
  • America/Kralendijk
  • America/La_Paz
  • America/Lima
  • America/Los_Angeles
  • America/Louisville
  • America/Lower_Princes
  • America/Maceio
  • America/Managua
  • America/Manaus
  • America/Marigot
  • America/Martinique
  • America/Matamoros
  • America/Mazatlan
  • America/Mendoza
  • America/Menominee
  • America/Merida
  • America/Metlakatla
  • America/Mexico_City
  • America/Miquelon
  • America/Moncton
  • America/Monterrey
  • America/Montevideo
  • America/Montreal
  • America/Montserrat
  • America/Nassau
  • America/New_York
  • America/Nipigon
  • America/Nome
  • America/Noronha
  • America/North_Dakota/Beulah
  • America/North_Dakota/Center
  • America/North_Dakota/New_Salem
  • America/Ojinaga
  • America/Panama
  • America/Pangnirtung
  • America/Paramaribo
  • America/Phoenix
  • America/Port-au-Prince
  • America/Port_of_Spain
  • America/Porto_Acre
  • America/Porto_Velho
  • America/Puerto_Rico
  • America/Punta_Arenas
  • America/Rainy_River
  • America/Rankin_Inlet
  • America/Recife
  • America/Regina
  • America/Resolute
  • America/Rio_Branco
  • America/Rosario
  • America/Santa_Isabel
  • America/Santarem
  • America/Santiago
  • America/Santo_Domingo
  • America/Sao_Paulo
  • America/Scoresbysund
  • America/Shiprock
  • America/Sitka
  • America/St_Barthelemy
  • America/St_Johns
  • America/St_Kitts
  • America/St_Lucia
  • America/St_Thomas
  • America/St_Vincent
  • America/Swift_Current
  • America/Tegucigalpa
  • America/Thule
  • America/Thunder_Bay
  • America/Tijuana
  • America/Toronto
  • America/Tortola
  • America/Vancouver
  • America/Virgin
  • America/Whitehorse
  • America/Winnipeg
  • America/Yakutat
  • America/Yellowknife
  • Antarctica/Casey
  • Antarctica/Davis
  • Antarctica/DumontDUrville
  • Antarctica/Macquarie
  • Antarctica/Mawson
  • Antarctica/McMurdo
  • Antarctica/Palmer
  • Antarctica/Rothera
  • Antarctica/South_Pole
  • Antarctica/Syowa
  • Antarctica/Troll
  • Antarctica/Vostok
  • Arctic/Longyearbyen
  • Asia/Aden
  • Asia/Almaty
  • Asia/Amman
  • Asia/Anadyr
  • Asia/Aqtau
  • Asia/Aqtobe
  • Asia/Ashgabat
  • Asia/Ashkhabad
  • Asia/Atyrau
  • Asia/Baghdad
  • Asia/Bahrain
  • Asia/Baku
  • Asia/Bangkok
  • Asia/Barnaul
  • Asia/Beirut
  • Asia/Bishkek
  • Asia/Brunei
  • Asia/Calcutta
  • Asia/Chita
  • Asia/Choibalsan
  • Asia/Chongqing
  • Asia/Chungking
  • Asia/Colombo
  • Asia/Dacca
  • Asia/Damascus
  • Asia/Dhaka
  • Asia/Dili
  • Asia/Dubai
  • Asia/Dushanbe
  • Asia/Famagusta
  • Asia/Gaza
  • Asia/Harbin
  • Asia/Hebron
  • Asia/Ho_Chi_Minh
  • Asia/Hong_Kong
  • Asia/Hovd
  • Asia/Irkutsk
  • Asia/Istanbul
  • Asia/Jakarta
  • Asia/Jayapura
  • Asia/Jerusalem
  • Asia/Kabul
  • Asia/Kamchatka
  • Asia/Karachi
  • Asia/Kashgar
  • Asia/Kathmandu
  • Asia/Katmandu
  • Asia/Khandyga
  • Asia/Kolkata
  • Asia/Krasnoyarsk
  • Asia/Kuala_Lumpur
  • Asia/Kuching
  • Asia/Kuwait
  • Asia/Macao
  • Asia/Macau
  • Asia/Magadan
  • Asia/Makassar
  • Asia/Manila
  • Asia/Muscat
  • Asia/Nicosia
  • Asia/Novokuznetsk
  • Asia/Novosibirsk
  • Asia/Omsk
  • Asia/Oral
  • Asia/Phnom_Penh
  • Asia/Pontianak
  • Asia/Pyongyang
  • Asia/Qatar
  • Asia/Qyzylorda
  • Asia/Rangoon
  • Asia/Riyadh
  • Asia/Saigon
  • Asia/Sakhalin
  • Asia/Samarkand
  • Asia/Seoul
  • Asia/Shanghai
  • Asia/Singapore
  • Asia/Srednekolymsk
  • Asia/Taipei
  • Asia/Tashkent
  • Asia/Tbilisi
  • Asia/Tehran
  • Asia/Tel_Aviv
  • Asia/Thimbu
  • Asia/Thimphu
  • Asia/Tokyo
  • Asia/Tomsk
  • Asia/Ujung_Pandang
  • Asia/Ulaanbaatar
  • Asia/Ulan_Bator
  • Asia/Urumqi
  • Asia/Ust-Nera
  • Asia/Vientiane
  • Asia/Vladivostok
  • Asia/Yakutsk
  • Asia/Yangon
  • Asia/Yekaterinburg
  • Asia/Yerevan
  • Atlantic/Azores
  • Atlantic/Bermuda
  • Atlantic/Canary
  • Atlantic/Cape_Verde
  • Atlantic/Faeroe
  • Atlantic/Faroe
  • Atlantic/Jan_Mayen
  • Atlantic/Madeira
  • Atlantic/Reykjavik
  • Atlantic/South_Georgia
  • Atlantic/St_Helena
  • Atlantic/Stanley
  • Australia/ACT
  • Australia/Adelaide
  • Australia/Brisbane
  • Australia/Broken_Hill
  • Australia/Canberra
  • Australia/Currie
  • Australia/Darwin
  • Australia/Eucla
  • Australia/Hobart
  • Australia/LHI
  • Australia/Lindeman
  • Australia/Lord_Howe
  • Australia/Melbourne
  • Australia/NSW
  • Australia/North
  • Australia/Perth
  • Australia/Queensland
  • Australia/South
  • Australia/Sydney
  • Australia/Tasmania
  • Australia/Victoria
  • Australia/West
  • Australia/Yancowinna
  • Brazil/Acre
  • Brazil/DeNoronha
  • Brazil/East
  • Brazil/West
  • CET
  • CST6CDT
  • Canada/Atlantic
  • Canada/Central
  • Canada/Eastern
  • Canada/Mountain
  • Canada/Newfoundland
  • Canada/Pacific
  • Canada/Saskatchewan
  • Canada/Yukon
  • Chile/Continental
  • Chile/EasterIsland
  • Cuba
  • EET
  • EST5EDT
  • Egypt
  • Eire
  • Etc/GMT
  • Etc/GMT+0
  • Etc/GMT+1
  • Etc/GMT+10
  • Etc/GMT+11
  • Etc/GMT+12
  • Etc/GMT+2
  • Etc/GMT+3
  • Etc/GMT+4
  • Etc/GMT+5
  • Etc/GMT+6
  • Etc/GMT+7
  • Etc/GMT+8
  • Etc/GMT+9
  • Etc/GMT-0
  • Etc/GMT-1
  • Etc/GMT-10
  • Etc/GMT-11
  • Etc/GMT-12
  • Etc/GMT-13
  • Etc/GMT-14
  • Etc/GMT-2
  • Etc/GMT-3
  • Etc/GMT-4
  • Etc/GMT-5
  • Etc/GMT-6
  • Etc/GMT-7
  • Etc/GMT-8
  • Etc/GMT-9
  • Etc/GMT0
  • Etc/Greenwich
  • Etc/UCT
  • Etc/UTC
  • Etc/Universal
  • Etc/Zulu
  • Europe/Amsterdam
  • Europe/Andorra
  • Europe/Astrakhan
  • Europe/Athens
  • Europe/Belfast
  • Europe/Belgrade
  • Europe/Berlin
  • Europe/Bratislava
  • Europe/Brussels
  • Europe/Bucharest
  • Europe/Budapest
  • Europe/Busingen
  • Europe/Chisinau
  • Europe/Copenhagen
  • Europe/Dublin
  • Europe/Gibraltar
  • Europe/Guernsey
  • Europe/Helsinki
  • Europe/Isle_of_Man
  • Europe/Istanbul
  • Europe/Jersey
  • Europe/Kaliningrad
  • Europe/Kiev
  • Europe/Kirov
  • Europe/Lisbon
  • Europe/Ljubljana
  • Europe/London
  • Europe/Luxembourg
  • Europe/Madrid
  • Europe/Malta
  • Europe/Mariehamn
  • Europe/Minsk
  • Europe/Monaco
  • Europe/Moscow
  • Europe/Nicosia
  • Europe/Oslo
  • Europe/Paris
  • Europe/Podgorica
  • Europe/Prague
  • Europe/Riga
  • Europe/Rome
  • Europe/Samara
  • Europe/San_Marino
  • Europe/Sarajevo
  • Europe/Saratov
  • Europe/Simferopol
  • Europe/Skopje
  • Europe/Sofia
  • Europe/Stockholm
  • Europe/Tallinn
  • Europe/Tirane
  • Europe/Tiraspol
  • Europe/Ulyanovsk
  • Europe/Uzhgorod
  • Europe/Vaduz
  • Europe/Vatican
  • Europe/Vienna
  • Europe/Vilnius
  • Europe/Volgograd
  • Europe/Warsaw
  • Europe/Zagreb
  • Europe/Zaporozhye
  • Europe/Zurich
  • GB
  • GB-Eire
  • GMT
  • GMT0
  • Greenwich
  • Hongkong
  • Iceland
  • Indian/Antananarivo
  • Indian/Chagos
  • Indian/Christmas
  • Indian/Cocos
  • Indian/Comoro
  • Indian/Kerguelen
  • Indian/Mahe
  • Indian/Maldives
  • Indian/Mauritius
  • Indian/Mayotte
  • Indian/Reunion
  • Iran
  • Israel
  • Jamaica
  • Japan
  • Kwajalein
  • Libya
  • MET
  • MST7MDT
  • Mexico/BajaNorte
  • Mexico/BajaSur
  • Mexico/General
  • NZ
  • NZ-CHAT
  • Navajo
  • PRC
  • PST8PDT
  • Pacific/Apia
  • Pacific/Auckland
  • Pacific/Bougainville
  • Pacific/Chatham
  • Pacific/Chuuk
  • Pacific/Easter
  • Pacific/Efate
  • Pacific/Enderbury
  • Pacific/Fakaofo
  • Pacific/Fiji
  • Pacific/Funafuti
  • Pacific/Galapagos
  • Pacific/Gambier
  • Pacific/Guadalcanal
  • Pacific/Guam
  • Pacific/Honolulu
  • Pacific/Johnston
  • Pacific/Kiritimati
  • Pacific/Kosrae
  • Pacific/Kwajalein
  • Pacific/Majuro
  • Pacific/Marquesas
  • Pacific/Midway
  • Pacific/Nauru
  • Pacific/Niue
  • Pacific/Norfolk
  • Pacific/Noumea
  • Pacific/Pago_Pago
  • Pacific/Palau
  • Pacific/Pitcairn
  • Pacific/Pohnpei
  • Pacific/Ponape
  • Pacific/Port_Moresby
  • Pacific/Rarotonga
  • Pacific/Saipan
  • Pacific/Samoa
  • Pacific/Tahiti
  • Pacific/Tarawa
  • Pacific/Tongatapu
  • Pacific/Truk
  • Pacific/Wake
  • Pacific/Wallis
  • Pacific/Yap
  • Poland
  • Portugal
  • ROK
  • Singapore
  • SystemV/AST4
  • SystemV/AST4ADT
  • SystemV/CST6
  • SystemV/CST6CDT
  • SystemV/EST5
  • SystemV/EST5EDT
  • SystemV/HST10
  • SystemV/MST7
  • SystemV/MST7MDT
  • SystemV/PST8
  • SystemV/PST8PDT
  • SystemV/YST9
  • SystemV/YST9YDT
  • Turkey
  • UCT
  • US/Alaska
  • US/Aleutian
  • US/Arizona
  • US/Central
  • US/East-Indiana
  • US/Eastern
  • US/Hawaii
  • US/Indiana-Starke
  • US/Michigan
  • US/Mountain
  • US/Pacific
  • US/Pacific-New
  • US/Samoa
  • UTC
  • Universal
  • W-SU
  • WET
  • Zulu