Hologres:Time zones

Introduction to time zones

View the default time zone

Change the client time zone

• Hologres supports the following formats for specifying a time zone.

  • Full time zone name, such as America/New_York.

  • UTC time zone offset: For example, an offset of -08:00:00 or UTC-08 corresponds to the UTC+8 time zone. This is because the POSIX time zone specification reverses the sign for GMT and UTC offsets. Therefore, a negative sign (-) indicates a time zone east of UTC.

The following table lists common time zones in Hologres and their corresponding parameter values.

• Change the client time zone

  By default, Hologres stores time in UTC. The default time zone for all regions is UTC+08 (China Standard Time). You can change the time zone displayed on the client by setting the time zone parameter with the following command.

  Note

  Changing the time zone affects only how it is displayed on the client. It does not alter the actual time zone information in the underlying storage.

  • Session level

    Use the SET command to configure a Grand Unified Configuration (GUC) parameter at the session level. Session-level parameters apply only to the current session and expire when the connection closes. Place the command before your SQL statement as follows.

    --Change the time zone to Canada/Eastern.
    set timezone ='Canada/Eastern';
    
    --Change to the fifth time zone west of UTC.
    set timezone ='05:30:00';           

  • Database level

    Use the ALTER DATABASE <db_name> SET <value>; command to configure a GUC parameter at the database level.

    Note

    After running this command:

    • The setting applies to the entire database. You must disconnect and reconnect for the change to take effect.

    • The setting does not apply to new databases. You must configure it manually for each new database.

    The usage is as follows.

    -- Change the database-level time zone to UTC-0.
    alter database <db_name> set timezone = 'UTC-0';
    
    -- Change the database-level time zone to UTC+5.
    alter database <db_name> set timezone = 'UTC+05';

Time type mapping between different data sources and Hologres

FAQ

• When I synchronize data in real time from DataHub to Hologres, the time in Hologres is eight hours behind the actual time. The field type is TIMESTAMP in both DataHub and Hologres.

  • Cause: The Hologres connector automatically converts the data's time zone to the zero time zone during the write process. If the Hologres field is set to TIMESTAMP, it does not include time zone information. The data is written in the zero time zone, which is eight hours behind.

  • Solution: Re-create the table and change the field type in Hologres to TIMESTAMPTZ.

• When I synchronize data in real time from DataHub to Hologres, the time displayed in Tableau differs by eight hours from the time in Hologres. The field type in Hologres is TIMESTAMPTZ.

  • Cause: This is a time zone display issue on the Tableau frontend.

  • Solution: HoloWeb uses UTC+08 as the default time zone. You can verify the data accuracy in HoloWeb. Then, when establishing a connection in Tableau, you can adjust the time zone in the Initial SQL field using the following command.

    --Display time in the UTC+8 time zone.
    set timezone to 'Asia/Shanghai';

• Why does +08 appear in the time display after I synchronize data from MySQL to Hologres?

  • Cause: +08 indicates that the current client is displaying time in the UTC+8 time zone. It does not mean the stored data itself is in the UTC+8 time zone.

  • Solution: Change the client's time zone to the desired time zone.

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

  The time zone displayed in JDBC defaults to the Java Virtual Machine (JVM) time zone. To change the time zone display in JDBC, connect to JDBC and run the following SQL statement.

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