Choosing the Right Date/Time Storage Type in MySQL and PostgreSQL

When developing software, storing timestamps correctly is essential for business logic, data integrity, and system stability. This article reviews common MySQL date/time types, explains why using string columns (e.g., VARCHAR) is a bad practice, and compares DATETIME , TIMESTAMP , and numeric Unix timestamps.

Why Not Store Dates as Strings

String columns waste storage space, make comparisons slower, prevent the use of built‑in date functions, and lead to inefficient indexing.

DATETIME vs TIMESTAMP

DATETIME stores the literal date‑time value without any time‑zone conversion. It is suitable when the application handles time zones itself.

TIMESTAMP converts the input value to UTC on write and back to the session time zone on read, making it ideal for multi‑time‑zone applications.

Example table definition:

CREATE TABLE `time_zone_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert a row (session time zone = UTC):

INSERT INTO time_zone_test(date_time, time_stamp) VALUES (NOW(), NOW());

Query the row:

SELECT date_time, time_stamp FROM time_zone_test;

Result (UTC):

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |
+---------------------+---------------------+

Change session time zone to UTC+8 and query again:

SET time_zone = '+8:00';

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |
+---------------------+---------------------+

PostgreSQL Equivalents

PostgreSQL does not have a DATETIME type. Use TIMESTAMP WITHOUT TIME ZONE (similar to MySQL DATETIME ) or TIMESTAMP WITH TIME ZONE ( TIMESTAMPTZ ) which behaves like MySQL TIMESTAMP .

Numeric Unix Timestamp

Storing seconds (or milliseconds) since 1970‑01‑01 as INT / BIGINT offers fast comparisons and easy cross‑system exchange, but sacrifices readability.

SELECT UNIX_TIMESTAMP('2020-01-11 09:53:32');  -- returns 1578707612
SELECT FROM_UNIXTIME(1578707612);          -- returns 2020-01-11 09:53:32

Comparison Table

Type

Storage

Format

Range

Time‑zone

DATETIME

5~8 bytes

YYYY‑MM‑DD hh:mm:ss[.fraction]

1000‑01‑01 … 9999‑12‑31

No

TIMESTAMP

4~7 bytes

YYYY‑MM‑DD hh:mm:ss[.fraction]

1970‑01‑01 … 2038‑01‑19

Yes

Numeric Unix timestamp

4 bytes

Integer (e.g., 1578707612)

After 1970‑01‑01

No

Recommendation Summary

Use TIMESTAMP when you need automatic time‑zone handling and your dates stay before 2038.

Use DATETIME when you control time zones in the application or need dates beyond 2038.

Use numeric Unix timestamps for maximum comparison performance and cross‑system portability, accepting reduced readability.