Unix time, also known as POSIX time or epoch time, is a system for representing time as the number of seconds that have elapsed since January 1, 1970, at 00:00:00 UTC. MySQL provides several flexible functions for working with Unix time, making it easy to manipulate and convert Unix timestamps in your database.
In this article, we’ll explore how to get the Unix timestamp in MySQL and how to convert a Unix timestamp to a human-readable date using the FROM_UNIXTIME() function.
Getting the Unix timestamp in MySQL
To get the Unix timestamp in MySQL, we can use the UNIX_TIMESTAMP() function. Here’s an example:
mysql
SELECT UNIX_TIMESTAMP(now());
In the above code, we use now() to get the current date and time, and then pass it to UNIX_TIMESTAMP(). This function returns the Unix timestamp as an integer.
We can also use UNIX_TIMESTAMP() directly in our MySQL statements. Here’s an example of inserting a row with a Unix timestamp:
mysql
INSERT INTO tableName VALUES('someField', 'someOtherField', UNIX_TIMESTAMP(now()));
In the above code, we insert a row into tableName with three fields, the third field being a Unix timestamp representing the current date and time.
Converting a Unix timestamp to a human-readable date
To convert a Unix timestamp to a human-readable date in MySQL, we can use the FROM_UNIXTIME() function. Here’s an example:
mysql
SELECT FROM_UNIXTIME(fieldName, '%Y-%m-%d');
In the above code, we use FROM_UNIXTIME() to convert the Unix timestamp stored in fieldName to a human-readable date in the format of YYYY-MM-DD.
We can also customize the format of the date using various format specifiers. For example, %Y represents the year in four digits, %m represents the month in two digits, and %d represents the day in two digits. Head over to the official FROM_UNIXTIME() function documentation for more information on how to customize the format of the date.
Conclusion
Working with Unix time in MySQL is a fundamental skill for any database developer. By mastering the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions, you can easily manipulate Unix timestamps and human-readable dates in your database. With these tools at your disposal, you can build powerful applications that work with time-based data.