FROM_UNIXTIME() function in MySQL

FROM_UNIXTIME() : This function in MySQL helps to return date /DateTime representation of a Unix timestamp. The format of returning value will be ‘YYYY-MM-DD HH:MM:SS’ or 'YYYYMMDDHHMMSS', depending on the context of the function. Syntax :

FROM_UNIXTIME(unix_timestamp, format)

Parameters : The function can accept two arguments as follows.

• unix_timestamp - It is an internal timestamp value and it's value can be produced by UNIX_TIMESTAMP() function.
• format - The way in which the resulting value will be formatted

Result : The function will return date /DateTime representation of a Unix timestamp. And the format of returning value will be ‘YYYY-MM-DD HH:MM:SS’ or 'YYYYMMDDHHMMSS', depending on the context of the function. Example-1 : Working of FROM_UNIXTIME() function with one parameter.

SELECT FROM_UNIXTIME(599462400) 
AS Unix;

Output :

Unix
1988-12-29 22:20:00

Example-2 : Working of FROM_UNIXTIME() function with fractional seconds.

SELECT FROM_UNIXTIME(599462445.99999) 
AS Unix;

Output :

Unix
1988-12-29 22:20:45.99999

Example-3 : Working of FROM_UNIXTIME() function when both parameters are passed.

• When format is '%W, %D %M %Y' -

  SELECT FROM_UNIXTIME(799462445, '%W, %D %M %Y') 
  AS Unix;

  Output :

  Unix
  Tuesday, 2nd May 1995

• When format is '%h:%i %p, %D %M %Y' -

  SELECT FROM_UNIXTIME(799462445, '%h:%i %p, %D %M %Y') 
  AS Unix;

  Output :

  Unix
  06:54 PM, 2nd May 1995

Example-4 : Working of FROM_UNIXTIME() function in Numeric context.

SELECT  
FROM_UNIXTIME(846562400) As 'String_form',
FROM_UNIXTIME(846562400) + 1 As 'Numeric_form';

Output :

String_form	Numeric_form
1996-10-28 21:13:20	19961028211321