MySQL Tutorial/Date Time Functions/FROM UNIXTIME

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Returns a unix_timestamp argument as a value in "YYYY-MM-DD HH:MM:SS" or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

The value is expressed in the current time zone.

mysql>
mysql>
mysql> SELECT FROM_UNIXTIME(875996580);
+--------------------------+
| FROM_UNIXTIME(875996580) |
+--------------------------+
| 1997-10-04 13:23:00      |
+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
+------------------------------+
| FROM_UNIXTIME(875996580) + 0 |
+------------------------------+
|        19971004132300.000000 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),"%Y %D %M %h:%i:%s %x");
+--------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(),"%Y %D %M %h:%i:%s %x") |
+--------------------------------------------------------+
| 2007 22nd July 07:46:11 2007                           |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

SELECT FROM_UNIXTIME(1111885200);

mysql>
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-26 18:00:00       |
+---------------------------+
1 row in set (0.00 sec)
mysql>

You can optionally give FROM_UNIXTIME function a format parameter

DATE_FORMAT and TIME_FORMAT Output Formats

format Parameter Output Format %r 12-hour time (hh:mm:ss (AM|PM)) %T 24-hour time (hh:mm:ss) %Y Numeric year, 4 digits %y Numeric year, 2 digits %m Month with leading 0 (01, 02-12) %c Month without leading 0 (1, 2-12) %M Month name (January, February, and so on) %b Month name, abbreviated (Jan, Feb, and so on) %D Day of the month with an English suffix (1st, 2nd, and so on) %d Day of the month with leading 0 (00, 01, 02-31) %e Day of the month without leading 0 (0, 1, 2-31) %W Weekday name (Sunday, Monday, and so on) %a Weekday name, abbreviated (Sun, Mon, and so on) %H Hour (00, 01-23) %k Hour (0, 1-23) %h Hour (01, 02-12) %I Hour (01, 02-12) %l Hour (1, 2-12) %i Minutes (00, 01-59) %S Seconds (00, 01-59) %s Seconds (00, 01-59) %P AM or PM %U Week number in the year,in which Sunday is the first day of the week $u Week number in the year,in which Monday is the first day of the week %X & %V Year and week number, respectively,in which Sunday is the first day of the week %x & %v Year and week number, respectively,in which Monday is the first day of the week %j Day of year with leading 0"s (001, 002-366) %w Weekday number (0=Sunday, 1=Monday, and so on) %% Literal %

mysql>
mysql> SELECT FROM_UNIXTIME(1000000000, "%W, %M");
+-------------------------------------+
| FROM_UNIXTIME(1000000000, "%W, %M") |
+-------------------------------------+
| Saturday, September                 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql>