Relationship Between Timestamps and Time Zones

A Unix timestamp represents seconds elapsed since midnight UTC of January 1, 1970, ignoring leap seconds. Although timestamps are absolute, their textual representation depends on the associated time zone.

public class ZoneDemo {
    public static void main(String[] args) throws Exception {
        java.util.TimeZone shanghaiTz = java.util.TimeZone.getTimeZone("Asia/Shanghai");
        java.util.TimeZone utcTz = java.util.TimeZone.getTimeZone("UTC");

        java.util.Date epoch = new java.util.Date(0L);
        System.out.println("Epoch in system default zone: " + epoch);

        java.text.SimpleDateFormat fmt = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        fmt.setTimeZone(shanghaiTz);
        System.out.println("Epoch in Shanghai zone: " + fmt.format(epoch));

        fmt.setTimeZone(utcTz);
        System.out.println("Epoch in UTC zone: " + fmt.format(epoch));

        fmt.setTimeZone(shanghaiTz);
        long shanghaiMillis = fmt.parse("2024-02-25 00:00:00").getTime();
        System.out.println("Shanghai zone timestamp for 2024-02-25 00:00:00: " + shanghaiMillis);

        fmt.setTimeZone(utcTz);
        long utcMillis = fmt.parse("2024-02-25 00:00:00").getTime();
        System.out.println("UTC zone timestamp for same literal: " + utcMillis);
    }
}

Observations:

• The same numeric timestamp renders different wall-clock times across zones.
• Identical date strings parsed under differing zones yield distinct timestamps. For example, Shanghai (UTC+8) and UTC differ by eight hours; (1708819200 - 1708790400) / 3600 confirms this offset.
• Time zone offsets determine the timestamp shift when interpreting a local date string.
• Equal wall times in different zones correspond to different moments global.

Inspecting and Adjusting the Time Zone in Java

import java.util.TimeZone;
import java.time.ZoneId;

public class JavaZoneCheck {
    public static void main(String[] args) {
        TimeZone tz = TimeZone.getDefault();
        ZoneId zid = ZoneId.systemDefault();
        System.out.println("Current TimeZone object: " + tz);
        System.out.println("System default ZoneId: " + zid);

        TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
        System.out.println("After setting default to UTC: " + TimeZone.getDefault());
    }
}

Inspecting and Adjusting MySQL Time Zone Settings

Inspection:

SHOW GLOBAL VARIABLES LIKE '%time_zone%';

Typical output:

• system_time_zone reflects the OS timezone where MySQL runs (e.g., UTC). Use SELECT NOW(); to compare server time with client time.
• time_zone set to SYSTEM means session time follows the system timezone.

Adjustment:

• System-level: On Linux, set OS timezone: cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime.
• Session-level:
  • SQL command: SET GLOBAL time_zone = '+08:00'; or SET GLOBAL time_zone = 'Asia/Shanghai';
  • Configuration file: Add default-time-zone=Asia/Shanghai in my.cnf.

How JDBC Deetrmines MySQL Session Time Zone

Using mysql-connector-j-8.0.33.jar, the method NativeProtocol.configureTimeZone resolves session time zone as follows:

1. Prefer JDBC URL parameters connectionTimeZone or serverTimezone.
2. If set to SERVER, query actual session time zone from MySQL at first use.
3. Fallback to JVM's default zone if no parameter is given.

Applying Time Zone Conversions in JDBC Operations

Assume Java runs in Asia/Shanghai (UTC+8) and MySQL uses UTC.

• Insertion: SqlTimestampValueEncoder.getString converts a Java instant to string using JDBC session zone, then MySQL stores it per its own zone. Example: 2024-02-25 11:52:56 (Shanghai) becomes 2024-02-25 03:52:56 (UTC).
• Retrieval: SqlTimestampValueFactory.localCreateFromDatetime parses stored UTC string into an instant, then formats to Shanghai time. Example: 2024-02-24 21:34:55 (UTC) becomes 2024-02-25 05:34:55 (Shanghai).

Core principle: Convert local date → instant (timestamp) → target zone date.

Issues Arising From Mismatched Time Zones

Discrepancies emerge when Java VM zone, JDBC parameter zone, and MySQL server zone diverge.

Case 1: Java zone == JDBC zone ≠ MySQL zone

Stored and displayed dates appear identical, but underlying timestamps differ. Internally:

• Store:
  1. Get timestamp in Java zone.
  2. Format using JDBC zone (no change here due to match).
  3. Send to MySQL; server reinterprets string in its own zone, altering timestamp.
• Read:
  1. Fetch UTC string from MySQL.
  2. Convert to timestamp via JDBC zone.
  3. Render in Java zone (matches original display, but timestamp mismatch persists).

Case 2: Java zone ≠ MySQL zone (JDBC zone ignored)

Displayed dates differ, but timestamps match:

• Store:
  1. Get Java timestamp.
  2. Convert to MySQL zone string (different wall time).
  3. Server stores matching timestamp despite different display.
• Read:
  1. Retrieve UTC string.
  2. Derive same timestamp.
  3. Render in Java zone (correct local time).

Case 3: Database-generated timestamps (e.g., DEFAULT CURRENT_TIMESTAMP)

• Store: MySQL assigns value purely based on its zone; no conversion from application.
• Read: Convert UTC timestamp to Java zone preserving moment; correct local time results.

Mitigation Strategies

• Align Java VM time zone, JDBC serverTimezone parameter, and MySQL server time zone.
• Alternatively, align Java and MySQL zones, avoiding explicit JDBC time zone settings to prevent unintended conversions.