Designing Cross‑Database Date and Time Types: Comparison of MySQL, Oracle, and SQL Server

When designing a product that needs to store date‑type values in a database while supporting multiple database systems, the author asks how to approach the design.

First, the article lists MySQL date‑time types— date (yyyy‑mm‑dd), time (hh:mm:ss), datetime (yyyy‑mm‑dd hh:mm:ss), timestamp (yyyymmddhhmmss), and year (yyyy)—and their storage ranges.

date: 1000‑01‑01 to 9999‑12‑31 (3 bytes)

time: –838:59:59 to 838:59:59 (3 bytes)

datetime: 1000‑01‑01 00:00:00 to 9999‑12‑31 23:59:59 (8 bytes)

timestamp: 1970‑01‑01 00:00:00 to 2037 (4 bytes)

year: 1901 to 2155 (1 byte)

For Oracle, the Date type occupies 7 bytes and stores century, year, month, day, hour, minute, second. The TIMESTAMP type adds fractional seconds and time‑zone support (13 bytes). INTERVAL types ( YEAR TO MONTH and DATE TO SECOND ) represent periods.

SQL Server’s relevant types are datetime (8 bytes, storing days since 1900‑01‑01 and milliseconds) and smalldatetime (4 bytes, storing days and minutes, range 1900‑01‑01 to 2079‑06‑06).

The author notes that if cross‑database compatibility is not required, using the native Date type is straightforward, but supporting multiple databases makes migration costly.

ORM handling of these types is questioned; the author previously avoided native date‑time types and stored timestamps as milliseconds via System.currentTimeMillis() , which provides an absolute UTC point but is hard to read and group.

SELECT username,user_addtime from userinfo

To improve readability, a custom database function date2str() converts the millisecond value to a formatted string in the desired time zone.

SELECT username,date2str(user_addtime) from userinfo

Another approach used a string format YYYYMMDDHHmmSST that includes a time‑zone indicator, sacrificing storage efficiency but aiding DBA maintenance and clarity.

In summary, the choice of date‑time field type should be driven by functional requirements, performance, development continuity, and maintenance considerations.