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