Storing and Handling Date‑Time in MySQL and Java: Types, Comparison, and Timezone Conversion

3  Storage of Date‑Time

The article introduces MySQL's date‑time data types (YEAR, DATE, TIME, DATETIME, TIMESTAMP) and explains their characteristics, storage size, and when to use each. It compares DATETIME (no timezone, larger range) with TIMESTAMP (timezone‑aware, 4‑byte storage) and gives selection guidelines.

3.1 MySQL Date‑Time Types Introduction

YEAR stores a 4‑digit year; DATE stores a calendar date (YYYY‑MM‑DD) similar to Java LocalDate; TIME stores a clock time (HH:MM:SS) similar to Java LocalTime; DATETIME combines DATE and TIME without timezone; TIMESTAMP stores an epoch‑based value, is timezone‑aware, and defaults to '0000‑00‑00 00:00:00' if not set.

3.2 Comparison and Choice

Key differences: DATETIME has no timezone, larger range (1000‑01‑01 to 9999‑12‑31); TIMESTAMP supports timezone conversion, smaller range (Unix 32‑bit), and uses only 4 bytes. Choose based on business needs for timezone handling and range.

Another option is to store Unix timestamps as INT in MySQL and long in Java, but this loses readability.

3.3 Interaction with Java

A mapping table (image) shows how MySQL types correspond to Java types. An example CREATE TABLE statement demonstrates defining columns with various date‑time types, including precision specifications for DATETIME(3) and TIMESTAMP(6):

CREATE TABLE `test_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time1` date DEFAULT NULL,
  `time2` time DEFAULT NULL,
  `time3` year(4) DEFAULT NULL,
  -- length 3, precise to milliseconds
  `time4` datetime(3) DEFAULT NULL,
  -- length 6, precise to microseconds
  `time5` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

When using MyBatis generator, generated models default to java.util.Date, which adds the system timezone and can be cumbersome. With MySQL 5.1.37+ and driver 4.2+, Java 8's new date‑time API (e.g., LocalDateTime, ZonedDateTime) can be used for a perfect match.

4  Timezone Conversion Operations

Timezone conversion requires treating the value as an absolute point on the timeline. Methods include:

4.1 Naïve addition/subtraction – directly adding the offset; not recommended.

4.2 Date + SimpleDateFormat – parse/format with pattern containing the zone ("z").

4.3 ZonedDateTime + DateTimeFormatter – Java 8 API for robust conversion.

4.4 Using timestamps – obtain epoch milliseconds and convert to Java objects with the desired zone.

4.5 Interaction with MySQL – retrieve MySQL stored time, convert to Java, then apply any of the above methods.

5  Summary

The article starts from practical problems developers face, explains Java date‑time acquisition, representation, and conversion (including Java 7 structures and Java 8 API), details MySQL date‑time types and selection advice, and provides multiple timezone conversion techniques for both Java and MySQL.