How to Choose the Right Date/Time Storage: Strings, DATETIME, TIMESTAMP, or Unix Timestamp

String Storage of Dates

Storing dates and times as plain strings (e.g., separate yyyy‑MM‑dd and HH:mm:ss columns) is generally discouraged because it leads to poor performance, cumbersome range queries, and complex comparisons.

DATETIME Type

The DATETIME type stores both date and time in the format yyyy‑MM‑dd HH:mm:ss with a range from 1000‑01‑01 00:00:00 to 9999‑12‑31 23:59:59. It does not include timezone information, so changing the server’s timezone does not affect stored values, which can cause inconsistencies across regions. When dealing with multiple timezones, it is advisable to store the timezone separately.

TIMESTAMP Type

TIMESTAMP also stores date and time in the same visual format but its valid range is limited to 1970‑01‑01 00:00:01 through 2038‑01‑19 03:14:07. Unlike DATETIME, TIMESTAMP records timezone information and many databases automatically convert values based on the server’s timezone, though driver behavior may vary. The limited range is a notable drawback.

Unix Timestamp (Absolute Time)

Unix timestamps represent the number of seconds (or milliseconds) elapsed since the epoch 1970‑01‑01 00:00:00 UTC. In Java you can obtain the current epoch value with:

System.currentTimeMillis();
// requires JDK 8+
Instant.now().toEpochMilli()

Storing this value as a BIGINT (or FLOAT for seconds) eliminates timezone concerns, simplifies numeric comparisons, and makes range queries straightforward (e.g., BETWEEN ?, ?). Display conversion can be handled on the client side or via database functions such as FROM_UNIXTIME:

If the stored unit is seconds, the division by 1000 is unnecessary.

Other Considerations

Reference manuals (e.g., Alibaba’s Java development guide) may recommend DATETIME, but the optimal choice depends on specific business requirements and deployment environments. After evaluating the trade‑offs, the author recommends using absolute Unix timestamps for most scenarios.