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.

   <source lang="sql">

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></source>

SELECT FROM_UNIXTIME(1111885200);

   <source lang="sql">

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

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 %

   <source lang="sql">

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