Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the date based on Unix time, which is the number of seconds since January 1, 1970, Greenwich Mean Time (GMT), with 12:00:01 being the first second of Unix time (the epoch). The second, optional argument formats the results using the formatting codes from DATE_FORMAT(). The function returns the date and time in the yyyy-mm-dd hh:mm:ss format, unless it’s part of a numeric expression. Then it returns the data in the yyyymmdd format. Here is an example:

SELECT FROM_UNIXTIME(0) AS 'My Epoch Start',
UNIX_TIMESTAMP( ) AS 'Now in Unix Terms',
FROM_UNIXTIME(UNIX_TIMESTAMP( )) AS 'Now in Human Terms';

+---------------------+-------------------+---------------------+
| My Epoch Start      | Now in Unix Terms | Now in Human Terms  |
+---------------------+-------------------+---------------------+
| 1969-12-31 18:00:00 |        1173928232 | 2007-03-14 22:10:32 | 
+---------------------+-------------------+---------------------+

Here we’re selecting the date based on zero seconds since the start of Unix time. The results are off by six hours because the server’s not located in the GMT zone. This function is typically used on columns whose values were derived from UNIX_TIMESTAMP(), as shown in the third field of the example.