Using a Unix timestamp in SQL databases is a common and highly effective strategy for managing time-based data. Storing time as a simple integer (the number of seconds since the Unix epoch) is space-efficient, easy to index, and simplifies timezone management.
This guide will show you how to handle the most common tasks in MySQL and PostgreSQL: how to convert a timestamp to a datetime and vice versa. We'll also cover best practices and recommend a free Unix timestamp converter for easy validation.
How to Convert Unix Timestamp to Date in SQL
When you retrieve a Unix timestamp from your database, you'll often need to convert it into a human-readable date format for reports or applications.
MySQL: FROM_UNIXTIME()
In MySQL, the FROM_UNIXTIME() function is used to perform this conversion.
SELECT FROM_UNIXTIME(1694870400) AS readable_date;
-- Output: '2023-09-16 08:00:00' (Note: Output may vary based on server timezone)
PostgreSQL: TO_TIMESTAMP()
PostgreSQL uses the TO_TIMESTAMP() function, which interprets the integer as seconds since the epoch.
SELECT TO_TIMESTAMP(1694870400) AS readable_date;
-- Output: '2023-09-16 08:00:00+08' (Note: Output includes timezone)
How to Convert Date to Unix Timestamp in SQL
To insert a new record or query against a timestamp column, you'll need to convert a datetime to a timestamp.
MySQL: UNIX_TIMESTAMP()
MySQL's UNIX_TIMESTAMP() function can take a date string and convert it into a 10-digit Unix timestamp.
SELECT UNIX_TIMESTAMP('2023-09-16 00:00:00') AS unix_ts;
-- Output: 1694822400
PostgreSQL: EXTRACT(EPOCH FROM ...)
PostgreSQL's method is slightly different. You first cast your date string to a TIMESTAMP type and then EXTRACT the EPOCH (total seconds) from it.
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-09-16 00:00:00') AS unix_ts;
-- Output: 1694822400
Best Practices for Handling Timestamps in SQL
When working with timestamp SQL columns, keep these points in mind for consistency and accuracy.
1. Manage Timezones
The biggest advantage of a Unix timestamp is that it's always based on UTC. To avoid confusion and offset errors, it's a best practice to configure your database servers and applications to operate in UTC. This ensures that 1694870400 means the exact same moment in time, regardless of where your servers are located.
2. Handle Milliseconds Correctly
Sometimes you may encounter a 13-digit Unix timestamp in milliseconds. Standard SQL functions like FROM_UNIXTIME and TO_TIMESTAMP expect seconds (a 10-digit value). To handle this, simply divide the millisecond timestamp by 1000 before passing it to the function.
-- Example for MySQL
SELECT FROM_UNIXTIME(1694870400000 / 1000) AS readable_date;
Verifying with a Unix Timestamp Converter
Before running complex queries or during debugging, it's incredibly helpful to verify your timestamps. A dedicated Unix timestamp converter is the perfect tool for this.
The DevUtils Unix Timestamp Converter is a free tool that helps you:
-
Quickly paste a Unix timestamp to check its corresponding human-readable date.
-
Instantly convert any date to a timestamp to get the correct integer for your SQL queries.
-
Ensure your data is correct without having to run a database query.
-
Work securely, as all conversions happen in your browser.
Mastering timestamp SQL functions is key to efficient database management. By combining these native SQL commands with a reliable Unix timestamp converter like DevUtils for validation, you can handle time-based data with confidence and precision.
