Choosing the Right MySQL Date/Time Type: INT vs TIMESTAMP vs DATETIME

When designing a new MySQL project, developers often encounter inconsistent date/time column types such as INT, TIMESTAMP, and DATETIME. This article explains the differences and guides you to choose the most appropriate type.

INT as a timestamp can store Unix epoch seconds, but it is limited to the year 2038 and cannot use DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP. These two clauses automatically fill the current time on insert and update, respectively.

TIMESTAMP (supported from MySQL 5.6) allows fractional seconds (up to 6) and also suffers the 2038 limitation. Unlike INT, it stores the value in UTC and converts it according to the session time zone, making it useful for automatic timezone handling.

Example of timezone conversion:

SELECT NOW();

After changing the server time zone to Qatar, the returned time changes accordingly, demonstrating TIMESTAMP's timezone awareness.

Because TIMESTAMP is tied to the time zone, each conversion calls the internal __tz_convert function, which acquires a lock. Under high concurrency this can become a performance bottleneck, so you should set the server time zone explicitly in the MySQL configuration file.

DATETIME has no 2038 limitation and can store values up to 9999-12-31 23:59:59. It does not have a built‑in time‑zone attribute, but it supports DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. Fractional seconds (DATETIME(N)) are also supported from MySQL 5.6.

Example: SELECT NOW(); DATETIME’s storage size is slightly larger than INT/TIMESTAMP, as shown below.

The DATE type stores only the date part without time, so it is not suitable when hours, minutes, and seconds are needed.

In summary, use DATETIME for most applications unless you specifically require automatic timezone conversion, in which case TIMESTAMP may be appropriate. Remember to configure the MySQL time zone to avoid hidden performance issues.

If you encounter the error unknown variable 'time_zone=Asia/Qatar', it means the MySQL time‑zone tables have not been initialized; loading the time‑zone data resolves the problem.