SQL Tutorial SQL Advanced SQL Database SQL References

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.

ValueDescription
%aWeekday name abbreviated (Sun to Sat)
%bMonth name abbreviated (Jan to Dec)
%cMonth as a numeric value (0 to 12)
%DDay of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%dDay of the month as a numeric value (01 to 31)
%eDay of the month as a numeric value (0 to 31)
%fMicroseconds (000000 to 999999)
%HHour (00 to 23)
%hHour (00 to 12)
%IHour (00 to 12)
%iMinutes (00 to 59)
%jDay of the year (001 to 366)
%kHour (00 to 23)
%lHour (1 to 12)
%MMonth name in full (January to December)
%mMonth name as a numeric value (00 to 12)
%pAM or PM
%rTime in 12 hour AM or PM format (hh:mm:ss AM/PM)
%SSeconds (00 to 59)
%sSeconds (00 to 59)
%TTime in 24 hour format (hh:mm:ss)
%UWeek where Sunday is the first day of the week (00 to 53)
%uWeek where Monday is the first day of the week (00 to 53)
%VWeek where Sunday is the first day of the week (01 to 53). Used with %X
%vWeek where Monday is the first day of the week (01 to 53). Used with %X
%WWeekday name in full (Sunday to Saturday)
%wDay of the week where Sunday=0 and Saturday=6
%XYear for the week where Sunday is the first day of the week. Used with %V
%xYear for the week where Monday is the first day of the week. Used with %v
%YYear as a numeric, 4-digit value
%yYear as a numeric, 2-digit value
%%A literal % character

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:

DataUnixLoginStamp
Data 11571995238
Data 21571995265
Data 31571995475
Data 41571995524
Data 51571995636

The query 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:

DataUnixLoginStampFROM_UNIXTIME_Value
Data 115719952382019-10-25 09:20:38
Data 215719952652019-10-25 09:21:05
Data 315719954752019-10-25 09:24:35
Data 415719955242019-10-25 09:25:24
Data 515719956362019-10-25 09:27:16

❮ MySQL Functions

5