SQL (MySQL) Unix Timestamp Guide
MySQL provides the UNIX_TIMESTAMP() function to get the current or converted timestamp, and FROM_UNIXTIME() to convert timestamps back to dates. These functions are essential for storing dates as integers and querying time-based data efficiently in MySQL databases.
Unix timestamps are a universal way to represent a specific moment in time as a single integer, counting the seconds elapsed since January 1, 1970 (the Unix epoch). Working with timestamps in SQL (MySQL) is a common task for developers building applications that log events, schedule jobs, compare dates, or communicate with APIs. The examples below cover the three most essential operations: retrieving the current timestamp, converting a timestamp into a human-readable date, and converting a date back into a timestamp.
Get Current Timestamp
The most common starting point is to capture the current moment as a Unix timestamp. In SQL (MySQL), you can obtain the number of seconds (or milliseconds) since the epoch using built-in functions. This value is useful for recording when an event occurred, setting cache expiry times, or generating time-based identifiers.
Get Current Unix Timestamp
-- Get current Unix timestamp
SELECT UNIX_TIMESTAMP();
-- Result: 1706745600
-- Current timestamp with precision
SELECT UNIX_TIMESTAMP(NOW(6));Convert Timestamp to Date
Once you have a Unix timestamp, you often need to display it in a human-readable format. Converting a raw integer like 1706745600 into a formatted date string such as "2024-02-01 00:00:00" makes it meaningful to end users. The following SQL (MySQL) code demonstrates how to perform this conversion with proper timezone handling.
Convert to Date
-- Convert Unix timestamp to datetime
SELECT FROM_UNIXTIME(1706745600);
-- Result: 2024-02-01 00:00:00
-- Custom format
SELECT FROM_UNIXTIME(1706745600, '%Y-%m-%d');
-- Result: 2024-02-01Convert Date to Timestamp
The reverse operation is equally important. When users provide a date through a form or when your application reads dates from a file, you need to convert them into Unix timestamps for storage, comparison, or transmission via APIs. Here is how to convert a date or date string into a Unix timestamp in SQL (MySQL).
Convert to Timestamp
-- Convert datetime to Unix timestamp
SELECT UNIX_TIMESTAMP('2024-02-01 00:00:00');
-- Result: 1706745600
-- From column
SELECT UNIX_TIMESTAMP(created_at) FROM users;Common Pitfalls in SQL (MySQL)
Working with timestamps can be error-prone, especially across different platforms and timezone configurations. Being aware of these common mistakes will help you write more robust SQL (MySQL) code and avoid subtle bugs that are difficult to trace in production.
- 1UNIX_TIMESTAMP() uses the session timezone — SET time_zone = '+00:00' for UTC
- 2MySQL's TIMESTAMP column type has a range of 1970-2038 only — use DATETIME for wider range
- 3FROM_UNIXTIME() returns NULL for timestamps outside the valid range
- 4UNIX_TIMESTAMP() returns 0 (not NULL) for invalid dates — validate inputs
Best Practices for Timestamp Handling
Regardless of the programming language, following a few best practices will keep your timestamp code reliable. Always store and transmit timestamps in UTC to avoid timezone ambiguity. Use seconds-based Unix timestamps unless your application requires sub-second precision, in which case milliseconds or microseconds are appropriate. When displaying dates to users, convert from UTC to their local timezone only at the presentation layer. Document whether your APIs expect seconds or milliseconds, as mixing the two is one of the most frequent sources of timestamp bugs.
Frequently Asked Questions
Related Tools
Timestamp Guides for Other Languages
Need Unix timestamp examples for a different programming language? Browse our complete collection of language-specific guides with copy-paste code snippets.