MySQL provides built-in functions for working with Unix timestamps. This guide covers all the essential operations you need to handle epoch time in MySQL, including getting the current timestamp, converting dates to timestamps, and converting timestamps back to human-readable dates.
How to get the current epoch time
SELECT unix_timestamp(now())More MySQL examples
Convert from human-readable date to epoch
SELECT unix_timestamp(time)Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD More on using Epoch timestamps with MySQL
Convert from epoch to human-readable date
FROM_UNIXTIME(epoch, optional output format)Default output format is YYY-MM-DD HH:MM:SS. If you need support for negative timestamps: DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),"%Y-%m-%d") (replace -315619200 with epoch) More MySQL
Additional MySQL Examples
Formatting Timestamps
SELECT FROM_UNIXTIME(1609459200, '%Y-%m-%d %H:%i:%s') AS formatted_date;Use format specifiers to customize the output format. Common formats: %Y (year), %m (month), %d (day), %H (hour), %i (minute), %s (second).
Working with Time Zones
SELECT CONVERT_TZ(FROM_UNIXTIME(1609459200), 'UTC', 'America/New_York') AS local_time;Convert timestamps to different time zones using CONVERT_TZ function.
About MySQL Unix Timestamps
MySQL provides built-in functions for working with Unix timestamps. The UNIX_TIMESTAMP() function and FROM_UNIXTIME() function are the most commonly used for timestamp operations. MySQL handles timezone conversions automatically and provides efficient timestamp storage and retrieval. This guide covers all essential timestamp operations in MySQL, including getting current timestamps, converting between timestamps and dates, and working with timezone-aware operations.
Related guides: Check out our guides for other databases: PostgreSQL, and programming languages: JavaScript, Python, and more. For timestamp conversion tools, visit our Tools page.
Frequently Asked Questions
How do I get the current Unix timestamp in MySQL?
Use UNIX_TIMESTAMP() to get the current Unix timestamp in seconds, or UNIX_TIMESTAMP(NOW()) for explicit current time. MySQL also supports FROM_UNIXTIME() for converting timestamps to dates.
How do I convert a Unix timestamp to a date in MySQL?
Use FROM_UNIXTIME(timestamp) to convert a Unix timestamp to a datetime. You can also format it: FROM_UNIXTIME(timestamp, "%Y-%m-%d %H:%i:%s"). MySQL handles timezone conversions automatically.
What is the difference between UNIX_TIMESTAMP() and TIMESTAMP columns?
UNIX_TIMESTAMP() returns a numeric Unix timestamp, while TIMESTAMP columns store datetime values. Use UNIX_TIMESTAMP() for calculations and TIMESTAMP columns for storage with automatic timezone handling.