FROM_UNIXTIME( )

This MySQL function returns the date based on Unix time, which is the number of seconds since January 1, 1970, Greenwich Mean Time (GMT).

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

FROM_UNIXTIME(unix_timestamp[, format])

Explanation

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.

Examples

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 UNIXTIME_STAMP(), as shown in the third field of the example.