MySQL FROM_UNIXTIME() Function
The MySQL FROM_UNIXTIME() function returns a date or datetime value from a given Unix timestamp. If format is not specified, this function returns value is in the following format:
- Returns result in 'YYYY-MM-DD HH:MM:SS' format, if used in a string context.
- Returns result in YYYYMMDDHHMMSS.uuuuuu format, if used in a numeric context.
If the format is specified, the result is formatted according to a given format string.
Syntax
FROM_UNIXTIME(unix_timestamp, format)
Parameters
unix_timestamp |
Required. Specify the Unix timestamp. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
format |
Optional. Specify a format string indicating the format of the return value. The following is a list of options for this parameter. It can be used in many combinations.
|
Return Value
Returns the date or datetime value.
Example 1:
The example below shows the usage of FROM_UNIXTIME() function.
mysql> SELECT FROM_UNIXTIME(1641883574); Result: '2022-01-11 06:46:14' mysql> SELECT FROM_UNIXTIME(1571969600, '%M %d, %Y'); Result: 'October 25, 2019' mysql> SELECT FROM_UNIXTIME(1571969600, '%M %e %Y'); Result: 'October 25 2019' mysql> SELECT FROM_UNIXTIME(1571969600, '%W, %M %e, %Y'); Result: 'Friday, October 25, 2019' mysql> SELECT FROM_UNIXTIME(1571969600, '%W'); Result: 'Friday' mysql> SELECT FROM_UNIXTIME(1571969600, '%r, %M %d, %Y'); Result: '02:13:20 AM, October 25, 2019'
Example 2:
Consider a database table called Sample with the following records:
Data | UnixLoginStamp |
---|---|
Data 1 | 1571995238 |
Data 2 | 1571995265 |
Data 3 | 1571995475 |
Data 4 | 1571995524 |
Data 5 | 1571995636 |
The statement given below can be used to convert the records of column UnixLoginStamp into date/datetime value.
SELECT *, FROM_UNIXTIME(UnixLoginStamp) AS FROM_UNIXTIME_Value FROM Sample;
This will produce the result as shown below:
Data | UnixLoginStamp | FROM_UNIXTIME_Value |
---|---|---|
Data 1 | 1571995238 | 2019-10-25 09:20:38 |
Data 2 | 1571995265 | 2019-10-25 09:21:05 |
Data 3 | 1571995475 | 2019-10-25 09:24:35 |
Data 4 | 1571995524 | 2019-10-25 09:25:24 |
Data 5 | 1571995636 | 2019-10-25 09:27:16 |
❮ MySQL Functions