MySQL UNIX_TIMESTAMP()

MySQL provides built-in functions for working with Unix timestamps. The function UNIX_TIMESTAMP() returns the current time as a seconds-based integer, while FROM_UNIXTIME() converts a timestamp back to a readable date.

Core functions:

-- Get current Unix timestamp
SELECT UNIX_TIMESTAMP();                    -- 1700000000

-- Convert a date to Unix timestamp
SELECT UNIX_TIMESTAMP('2024-01-15 09:30:00');  -- 1705310600

-- Convert Unix timestamp to date
SELECT FROM_UNIXTIME(1700000000);           -- '2023-11-14 22:13:20'

-- With formatting
SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%d');  -- '2023-11-14'

TIMESTAMP vs DATETIME column types:

Feature	TIMESTAMP	DATETIME
Storage	4 bytes	5 bytes (MySQL 5.6.4+)
Range	1970-01-01 to 2038-01-19	1000-01-01 to 9999-12-31
Timezone	Stored as UTC, converted on read	Stored as-is, no conversion
Default	Can auto-set to CURRENT_TIMESTAMP	Can auto-set (5.6.5+)

Timezone behavior is the key difference. A TIMESTAMP column stores values in UTC and converts them to the session's timezone on retrieval. A DATETIME stores exactly what you insert, with no conversion. This means changing your server's timezone or the session's time_zone variable will change the values you read from TIMESTAMP columns but not DATETIME columns.

Common pitfalls:

The TIMESTAMP type has the Y2038 limitation — it cannot store dates after January 19, 2038. For future dates (subscription expirations, scheduled events), use DATETIME instead. Also, UNIX_TIMESTAMP() returns 0 for dates outside the TIMESTAMP range rather than raising an error, which can silently corrupt your data.

Performance tip: Indexing a TIMESTAMP or DATETIME column works well for range queries. If you need to store Unix timestamps as integers for compatibility with external systems, use a BIGINT column to avoid the 2038 problem and to store millisecond-precision values.