Time Zone Pitfall When Migrating from MySQL to GBase 8c —...

When moving from MySQL to GBase 8c, the China-domestically developed database from GBASE, time zone configuration can surprise you. What works perfectly in MySQL may yield results off by 16 hours in GBase 8c — all because of a simple sign reversal.

This article reproduces the issue, walks through the diagnosis, and gives you two clean solutions to keep your timestamps accurate in a gbase database environment.

The Problem

MySQL offers two common ways to set the time zone:

-- Offset notation
SET time_zone = '+08:00';

-- Named time zone
SET time_zone = 'Asia/Shanghai';

Both work as expected.

Applying the same approach in GBase 8c, however, produces unexpected behavior:

swjtdb=# show timezone;
 TimeZone 
----------
 PRC
(1 row)

swjtdb=# select now();
       now()        
---------------------
 2025-10-19 22:23:18

swjtdb=# SET timezone = '+08:00';
SET

swjtdb=# select now();
       now()        
---------------------
 2025-10-19 06:23:39.868943-08  -- 16 hours behind

Notice the offset is shown as -08 instead of +08, resulting in a 16-hour gap. Switching to a named zone like SET timezone = 'Asia/Shanghai' immediately corrects the display.

Diagnosis

Using AT TIME ZONE conversions reveals the root cause:

WITH test_times AS (
    SELECT '2025-10-19 12:00:00'::timestamptz as test_ts
)
SELECT 
    test_ts,
    test_ts AT TIME ZONE 'UTC' as as_utc,
    test_ts AT TIME ZONE '+08:00' as as_beijing,
    test_ts AT TIME ZONE '-08:00' as as_negative
FROM test_times;

Result:

       test_ts         |       as_utc        |     as_beijing      |     as_negative     
------------------------+---------------------+---------------------+---------------------
 2025-10-19 12:00:00+08 | 2025-10-19 04:00:00 | 2025-10-18 20:00:00 | 2025-10-19 12:00:00

test_ts is noon Beijing time (+08)
AT TIME ZONE '+08:00' returns 8 PM the previous day, acting like UTC-8
AT TIME ZONE '-08:00' returns the correct noon

Confirming the sign flip:

SELECT 
    '2025-10-19 12:00:00'::timestamptz as base_time,
    ('2025-10-19 12:00:00'::timestamptz AT TIME ZONE '+08:00') as plus_eight,
    ('2025-10-19 12:00:00'::timestamptz AT TIME ZONE '-08:00') as minus_eight;

Result:

      base_time        |     plus_eight      |     minus_eight     
------------------------+---------------------+---------------------
 2025-10-19 12:00:00+08 | 2025-10-18 20:00:00 | 2025-10-19 12:00:00

Key takeaway: In GBase 8c, +08:00 is interpreted as UTC-8, and -08:00 as UTC+8 — exactly the opposite of what many developers expect.

Solutions

Option 1: Simplified Numeric Syntax (Recommended)

GBase 8c accepts a signless number, which bypasses the inversion entirely:

SET timezone = '8';    -- cleanest
SET timezone = '08';   -- two-digit form

For fractional offsets like +01:30, use a decimal:

SET timezone = '1.5';   -- equivalent to +01:30
-- or
SET timezone = '-01:30'; -- same effect but counter‑intuitive

Verify:

SHOW TimeZone;  -- returns 08:00:00
SELECT now();   -- shows correct UTC+8 time

Option 2: Named Time Zones (Standard Practice)

Named zones eliminate any ambiguity and are the safest choice across platforms:

SET timezone = 'Asia/Shanghai';
SET timezone = 'PRC';

-- List available time zones
SELECT name, utc_offset 
FROM pg_timezone_names 
WHERE utc_offset = '08:00:00'::interval;

Best Practices

Prefer named zones: SET timezone = 'Asia/Shanghai' is self‑documenting and consistent.
Fallback to simplified numeric syntax: SET timezone = '8' avoids the sign‑flip trap.
Avoid ISO offset notation when migrating from MySQL: for East‑Eight, set -08:00, not +08:00 — which is the opposite of MySQL and of instinct.

After any time zone change, a quick SELECT now() is the simplest way to confirm correctness and prevent subtle data corruption when running a gbase database.

推荐订阅源

DEV Community