Hologres:Time zone

Background information

Query the default time zone on a client

show timezone;

Modify the time zone setting on a client

• Use the relevant system table

  The PostgreSQL system table named pg_timezone_names stores the information about default time zones of different countries and regions. You can execute the following statement to query the time zones of different countries and regions in the system table:

  select * from pg_timezone_names;

  The following table describes fields in the system table.

  Field	Data type	Description
  name	text	The name of the time zone.
  abbrev	text	The abbreviation of the time zone.
  utc_offset	interval	The UTC offset of the time zone. A positive offset indicates a time zone in the Western Hemisphere. A negative offset indicates a time zone in the Eastern Hemisphere.
  is_dst	boolean	Indicates whether daylight saving time (DST) is applied. If yes, the value of this parameter is true. Otherwise, the value of this parameter is false.

  The following table describes common time zones in Hologres.

  Region	Time zone	Full time zone name	Offset from UTC+0
  All regions in China Singapore Malaysia (Kuala Lumpur)	Eighth time zone in the Eastern Hemisphere	PRC Asia/Shanghai Asia/Singapore Asia/Kuala_Lumpur	UTC-08
  Japan (Tokyo)	Ninth time zone in the Eastern Hemisphere	Asia/Tokyo	UTC-09
  Indonesia (Jakarta)	Seventh time zone in the Eastern Hemisphere	Asia/Jakarta	UTC-07
  Germany (Frankfurt)	First time zone in the Eastern Hemisphere	Europe/Berlin	UTC-01
  US (Virginia)	Fifth time zone in the Western Hemisphere	US/Eastern America/New_York	UTC+05
  US (Silicon Valley)	Eighth time zone in the Western Hemisphere	US/Pacific America/Los_Angeles	UTC+08

• Modify the time zone setting on a client

  By default, all time zones in Hologres are stored as UTC offsets. The default time zone of all Alibaba Cloud regions is UTC-08. You can modify the time zone setting on a client by using the TimeZone parameter. You can execute the following statements to modify the time zone setting on the client.

  Note

  • After you set the TimeZone parameter, only the time zone displayed on the client is changed, and the time zones of the stored data remain unchanged.

  • The default time zone displayed in HoloWeb is UTC-08.

  • Modify the time zone setting at the session level

    You can run the SET command to configure the Grand Unified Configuration (GUC) parameter at the session level. If you set the parameter at the session level, the setting takes effect only in the current session. After the session is closed, the setting becomes invalid. We recommend that you run the SET command before the SQL statement. Examples:

    -- Change the time zone to Canada/Eastern.
    set timezone ='Canada/Eastern';
    
    -- Change the time zone to the fifth time zone in the Eastern Hemisphere.
    set timezone ='05:00:00';           

  • Modify the time zone setting for a database

    You can execute the alter database <db_name> set <value>; statement to configure the GUC parameter for a database.

    Note

    Take note of the following items when you execute the statement:

    • To make the modification take effect for a database, disconnect the database from and then reconnect the database to the client.

    • The modification does not automatically apply to a new database. You must manually configure the GUC parameter for the new database.

    Examples:

    -- Change the time zone of a database to UTC+0.
    alter database <db_name> set timezone = 'UTC-0';
    
    -- Change the time zone of a database to the fifth time zone in the Western Hemisphere.
    alter database <db_name> set timezone = 'UTC+05';

Mappings of time-related data types between Hologres and other data sources

FAQ

• When I synchronize data of the TIMESTAMP type from DataHub to Hologres in real time, the data type remains unchanged, but the time values written to Hologres are 8 hours earlier than the original time values. Why?

  • Possible cause: The Hologres connector automatically converts the time zone value in the original data into UTC+0 when you write data. If you use the TIMESTAMP data type in Hologres, the time values written to Hologres are considered to be in UTC+0 because data of the TIMESTAMP type contains no time zone value. The UTC+0 time zone is 8 hours earlier than the default time zone in Hologres.

  • Solution: Create a table in Hologres and change the mapped data type from TIMESTAMP to TIMESTAMPTZ.

• When I synchronize data from DataHub to Hologres in real time, the data type is converted to TIMESTAMPTZ in Hologres. However, when I use Tableau to view the synchronized data, the displayed data has an 8-hour offset from the data stored in Hologres. Why?

  • Possible cause: The time zone displayed on the data page of Tableau is improper.

  • Solution: The default time zone in HoloWeb is UTC+08. Go to HoloWeb to check whether the displayed time zone is suitable for your business data. You can modify the time zone setting when you connect Tableau to Hologres by using the Initial SQL feature. Sample statement:

    -- Change the time zone displayed in Tableau to UTC+08.
    set timezone to 'Asia/Shanghai';

• Why does the time data displayed in Hologres contain the value +08 after I synchronize the data from MySQL to Hologres?

  • Possible cause: The value +08 indicates the time zone on the current client, rather than the time zone of the synchronized data.

  • Solution: Modify the time zone setting on the client based on your business requirements.

• How do I modify the time zone setting in Java Database Connectivity (JDBC)?

  By default, the time zone displayed in JDBC is the same as that in Java Virtual Machine (JVM). If you want to modify the time zone setting in JDBC, use JDBC to connect to Hologres and execute the following statement:

  -- Change the time zone to UTC+07.
  set timezone = '+07';