Overview
MySQL can convert Unix timestamps with FROM_UNIXTIME and UNIX_TIMESTAMP. The result can depend on session timezone settings, so production systems should be deliberate about storing UTC and formatting for users at the edge.
This guide focuses on practical implementation choices: how values should be stored, how they should cross API boundaries, and where developers most often introduce timezone or precision bugs. The safest pattern is to keep the stored value unambiguous, document the unit, and format the date only when it reaches a human-facing interface.
When to use it
Use MySQL timestamp conversion for reporting, imports, debugging, and legacy schemas that store epoch seconds as integers.
For production systems, also consider how the value will be indexed, logged, serialized, and read by other teams. A timestamp field that is obvious in one programming language can become ambiguous when it is consumed by JavaScript, SQL, mobile clients, or third-party integrations.
Developer examples
Use examples like this as a starting point, then adapt the timezone and precision to your application contract.
SELECT FROM_UNIXTIME(1717243200) AS converted_time;
SELECT UNIX_TIMESTAMP('2024-06-01 12:00:00') AS unix_seconds;Common pitfalls
Connection timezone affects interpretation. Document whether integer columns store seconds or milliseconds.
Most timestamp bugs come from hidden assumptions: local time treated as UTC, seconds treated as milliseconds, formatted strings parsed without offsets, or narrow integer columns copied from old examples. Add tests for boundary dates and document the expected unit beside every external timestamp field.
FAQ
Can MySQL store Unix timestamps as integers?
Yes, but use a range-safe type and document the unit.
Does FROM_UNIXTIME return UTC?
It returns a value according to the MySQL session timezone unless configured otherwise.