All Products
Search

This Product

    Hologres:Time zones

    Document Center

    Hologres:Time zones

    Last Updated:Mar 26, 2026

    Hologres stores all timestamps internally in Coordinated Universal Time (UTC) and converts them to the client's configured time zone at display time. This topic explains how time zones work in Hologres, how to configure them at the session or database level, and which timestamp data types to use when synchronizing data from external sources.

    Key concepts

    GMT and UTC

    Two standards define the zero time zone:

    • Greenwich Mean Time (GMT): Based on astronomical observations from the Royal Observatory in Greenwich, UK. Formerly used as the world standard time.

    • Coordinated Universal Time (UTC): The current world standard. Because Earth's rotation slows fractionally each year, the organization managing UTC periodically inserts a leap second to keep atomic time aligned with astronomical time.

    GMT is not equivalent to UTC — GMT equals UTC+0. The world is divided into 24 time zones (12 east, 12 west), each spanning 15 degrees of longitude and representing a one-hour difference. For example, +08 denotes UTC+8, which is eight hours ahead of UTC.

    POSIX sign convention in Hologres

    Hologres is compatible with the PostgreSQL protocol, which follows the POSIX time zone specification.

    Important

    The POSIX specification reverses the sign of UTC offsets compared to the ISO 8601 convention used in most date-time values. A negative sign (-) in a Hologres SET command indicates a time zone east of UTC; a positive sign (+) indicates west. For example, UTC-08 or -08:00:00 in a SET command corresponds to UTC+8 (China Standard Time), not UTC-8.

    The default time zone for all Hologres regions is UTC-08 in POSIX notation, which maps to China Standard Time (eight hours ahead of UTC).

    Timestamp data types

    Hologres provides two timestamp data types:

    Data type Time zone support Internal storage Display behavior Precision Example
    TIMESTAMP None Stores the value exactly as written, with no conversion Returns the raw stored value regardless of the client time zone Microsecond 2022-01-01 01:01:01.123456
    TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) Yes Converts the input from the client time zone to UTC before storing Converts the stored UTC value back to the client time zone (based on the TimeZone parameter) at query time Millisecond 2022-02-01 10:33:20.125+08
    Use TIMESTAMPTZ for any data that carries time zone information. When the client time zone changes, TIMESTAMPTZ values display correctly adjusted; TIMESTAMP values do not change.

    View the current time zone

    View the client's current time zone:

    SHOW timezone;

    In HoloWeb, the result shows the instance's default time zone (PRC), which corresponds to UTC+8. If you use a different client tool and the result differs, the client's time zone has been modified locally.

    View time zones for all regions:

    Hologres supports the pg_timezone_names system table from PostgreSQL, which lists time zone information for every region.

    SELECT * FROM pg_timezone_names;

    The table has the following columns:

    Column Type Description
    name text Time zone name
    abbrev text Time zone abbreviation
    utc_offset interval Offset from UTC. Positive (+) = east of UTC; negative (-) = west of UTC
    is_dst boolean t if daylight saving time is currently in effect; f otherwise

    Set the client time zone

    Changing the client time zone affects only how timestamps are displayed. It does not alter the underlying UTC values stored in the database.

    Supported time zone formats

    Hologres accepts two formats when specifying a time zone:

    • Full time zone name — a named time zone that encodes daylight saving time rules. Examples: America/New_York, Asia/Shanghai, Asia/Tokyo. This format is recommended because it handles daylight saving time transitions automatically.

    • UTC offset — a fixed offset from UTC, expressed in POSIX notation (sign reversed). Examples: UTC-08 or -08:00:00 for China Standard Time (UTC+8); UTC+05 for UTC-5.

    The following table lists common time zones and their corresponding values:

    Countries and regions Time zone Full time zone name UTC offset
    China, Singapore, Malaysia (Kuala Lumpur) UTC+08 PRC, Asia/Shanghai, Asia/Singapore, Asia/Kuala_Lumpur UTC+08
    Japan (Tokyo) UTC+09 Asia/Tokyo UTC+09
    Indonesia (Jakarta) UTC+07 Asia/Jakarta UTC+07
    Germany (Frankfurt) UTC+01 Europe/Berlin UTC+01
    US (Virginia) UTC-05 US/Eastern, America/New_York UTC-05
    US (Silicon Valley) UTC-08 US/Pacific, America/Los_Angeles UTC-08

    Session level

    Use the SET command to change the time zone for the current session. The setting applies only while the connection is open and is discarded when the session ends.

    -- Set the time zone to Canada/Eastern (full name format).
SET timezone = 'Canada/Eastern';

-- Set the time zone to the fifth time zone west of UTC (offset format).
SET timezone = '05:30:00';

    Database level

    Use ALTER DATABASE to set a default time zone for an entire database. This persists across sessions.

    Note

    After running this command:

    • The new setting applies to all subsequent connections to that database. You must disconnect and reconnect for the change to take effect.

    • The setting does not automatically apply to newly created databases. Configure each new database separately.

    -- Set the database-level time zone to UTC.
ALTER DATABASE <db_name> SET timezone = 'UTC-0';

-- Set the database-level time zone to UTC+5.
ALTER DATABASE <db_name> SET timezone = 'UTC+05';

    Time type mapping from external data sources

    When synchronizing data from external sources, use the mappings in the following table to avoid time zone drift or data inconsistencies.

    Data source Source data type Source example Hologres data type Hologres example Notes
    MySQL DATETIME 2001-07-14 02:14:19 TIMESTAMP 2001-07-14 02:14:19 MySQL DATETIME has no time zone. Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59. Map to TIMESTAMP to preserve the raw value.
    MySQL TIMESTAMP 2019-02-23 05:21:16 TIMESTAMPTZ 2019-02-23 05:21:16+08 MySQL TIMESTAMP stores UTC and converts on read using the server time zone. Range: 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. Map to TIMESTAMPTZ.
    MaxCompute DATETIME 2021-11-29 00:01:00 TIMESTAMPTZ 2021-11-29 00:01:00.000+08 MaxCompute DATETIME is UTC-based with millisecond precision. Range: 0000-1-1 to 9999-12-31. Map to TIMESTAMPTZ.
    MaxCompute TIMESTAMP 2021-01-11 00:00:00.123456789 TIMESTAMPTZ 2021-01-11 00:00:00.123+08 MaxCompute TIMESTAMP is UTC-based with nanosecond precision. Range: 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999. Hologres truncates nanoseconds to milliseconds. Map to TIMESTAMPTZ.
    Flink TIMESTAMP 2007-04-30 13:10:02.047 TIMESTAMPTZ 2007-04-30 13:10:02.047+08 Flink TIMESTAMP is UTC-based with millisecond precision. Map to TIMESTAMPTZ.
    DataHub TIMESTAMP 2020-03-05 10:10:00.123456+08 TIMESTAMPTZ 2020-03-05 10:10:00.123+08 The Hologres connector automatically converts the data's time zone to the zero time zone during the write process. Map to TIMESTAMPTZ.

    FAQ

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

    The Hologres connector automatically converts timestamps to UTC (zero time zone) before writing to Hologres. If the target field is TIMESTAMP (no time zone info), Hologres stores the UTC value as-is — without any offset — so it appears eight hours behind China Standard Time. Re-create the table with the field type set to TIMESTAMPTZ.

    When I sync data in real time from DataHub to Hologres, the time displayed in Tableau is eight hours off from what I see in HoloWeb. The field type in Hologres is `TIMESTAMPTZ`.

    This is a time zone display issue in Tableau. HoloWeb defaults to UTC+8, so the values appear correct there. In Tableau, set the time zone in the Initial SQL field when configuring the connection:

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

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

    +08 means the client is currently displaying timestamps in the UTC+8 time zone. It reflects the display offset, not the storage format of the data itself. To change the display, set the client time zone to the desired value.

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

    The JDBC driver defaults to the Java Virtual Machine (JVM) time zone. To override this for a specific session, run a SET command after connecting:

    -- Display time in the UTC+7 time zone.
SET timezone = '+07';