MySQL FROM_UNIXTIME() function

FROM_UNIXTIME() function

MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp. The return value is in ‘YYYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS.uuuuuu format depending upon the context of the function ( whether numeric or string). If specified, the result is formatted according to a given format string.

This function is useful in -

• FROM_UNIXTIME() is useful when you have Unix timestamps and want to represent them as dates and times in a readable format.
• In graphical presentations, FROM_UNIXTIME() can be used to label axes or data points with human-readable date and time values.
• When processing data that includes Unix timestamps, FROM_UNIXTIME() is used to transform the timestamps for better readability.
• FROM_UNIXTIME() is valuable for converting Unix timestamp-based data into a format that's compatible with MySQL's date and datetime columns.
• FROM_UNIXTIME() enables calculations that involve Unix timestamps, such as finding the time difference between two Unix timestamps in a readable format.
• The function is valuable for data analysis tasks that involve Unix timestamps, allowing you to work with time-based data.
• When analyzing log files or data that uses Unix timestamps to record events, FROM_UNIXTIME() helps convert timestamps into meaningful time representations.

Syntax:

FROM_ UNIXTIME (unix_timestamp, [format ])

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL FROM_UNIXTIME() function

The following statement will return a date time value from 1255033470.

Code:

SELECT FROM_UNIXTIME(1255033470);

Output:

mysql> SELECT FROM_UNIXTIME(1255033470);
+---------------------------+
| FROM_UNIXTIME(1255033470) |
+---------------------------+
| 2009-10-08 13:24:30       | 
+---------------------------+
1 row in set (0.01 sec)

Example: FROM_UNIXTIME() function in numeric format

The following statement will return a datetime value in numeric format from 1255033470.

Code:

SELECT FROM_UNIXTIME(1255033470)+0;

Output:

mysql> SELECT FROM_UNIXTIME(1255033470)+0;
+-----------------------------+
| FROM_UNIXTIME(1255033470)+0 |
+-----------------------------+
|       20091008132430.000000 | 
+-----------------------------+
1 row in set (0.00 sec)

Example: FROM_UNIXTIME() function with format sperifier

The following statement will return a date time value from 1255033470. The datetime is formatted according to the format string %Y %D %M %h:%i:%s %x.

Code:

SELECT FROM_UNIXTIME(1255033470, '%Y %D %M %h:%i:%s %x');

Output:

mysql> SELECT FROM_UNIXTIME(1255033470, '%Y %D %M %h:%i:%s %x');
+---------------------------------------------------+
| FROM_UNIXTIME(1255033470, '%Y %D %M %h:%i:%s %x') |
+---------------------------------------------------+
| 2009 8th October 01:24:30 2009                    | 
+---------------------------------------------------+
1 row in set (0.00 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: FROM_DAYS()
Next: GET_FORMAT()