Which Date/Time Type Should You Use in Databases? A Practical Guide

Date and time are essential components of every system and database design, yet they are often overlooked, and many developers are unaware of the implications of different storage types.

Date now = new Date(); // call insert or update method to create or update date field.

When designing a new system that spans multiple time zones, it is crucial to understand the pros and cons of each date/time type.

String Storage of Dates

Storing dates and times as strings (e.g., yyyy-MM-dd and HH:mm:ss in separate fields) is generally discouraged because it leads to poor performance, complicated comparisons, and range queries.

DateTime Type

The DateTime type stores both date and time in the format yyyy-MM-dd HH:mm:ss, covering the range from 1000-00-00 00:00:00 to 9999-12-31 23:59:59, but it does not include timezone information, so changing the server timezone does not affect stored values, potentially causing inconsistencies.

For cross‑timezone scenarios, a separate field for the timezone may be needed.

Timestamp Type

Timestamp also stores date and time in the same format as DateTime, but its range is limited to 1970-01-01 00:00:01 to 2038-01-19 03:14:07. It records timezone information, and some databases automatically convert based on the server timezone, though behavior varies across implementations.

Unix Timestamp

A Unix timestamp represents the number of seconds (or milliseconds) elapsed since the epoch (1970‑01‑01 00:00:00 UTC), eliminating timezone issues by using an absolute numeric value.

System.currentTimeMillis();

Instant.now().toEpochMilli()

Storing the absolute timestamp as a BIGINT or FLOAT allows simple numeric comparisons and easy conversion to formatted strings using database functions.

Timezone problems disappear because the value is absolute.

Comparisons are straightforward using numeric operators or BETWEEN clauses.

Display conversion is easy on any client by applying the appropriate timezone offset.

MySQL provides functions such as FROM_UNIXTIME to convert stored milliseconds to a readable format.

Other Considerations

Some companies, like Alibaba, still use the DateTime type in their development guidelines, but practices vary by business needs; referencing best practices is helpful, but solutions must be tailored to specific scenarios.

After evaluating the options, the author decided to adopt absolute Unix timestamps for handling date and time.