DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

Time Zone Pitfall When Migrating from MySQL to GBase 8c — and How to Fix It

#gbase #database #数据库 #mysql

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';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Verify:

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

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)