MySQL format date function: the difference between DATE_FORMAT, FROM_UNIXTIME and UNIX_TIMESTAMP

DATE_FORMATFormat the date
FROM_UNIXTIMEFormat the timestamp as a date
UNIX_TIMESTAMPFormat the date as a timestamp

1. DATE_FORMAT()

  • Display date/time data in different formats
  • Syntax: DATE_FORMAT(date, format)
    date: legal date
    format: specifies the date/time output format, the available formats are:
formatdescription
%aAbbreviated weekday
%bAbbreviated month name
%cMonth, number
%DDay of the month with English prefix
%dDay of month, numeric value (00-31)
%eDay of the month, numeric value (0-31)
%fMicroseconds
%HHour (00-23)
%hHour (01-12)
%IHour (01-12)
%iMinutes, number (00-59)
%jDay of the year (001-366)
%kHour (0-23)
%lHour (1-12)
%MMonth name
%mMonth, number (00-12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss AM or PM)
%SSeconds (00-59)
%sSeconds (00-59)
%TTime, 24-hours (hh:mm:ss)
%UWeek (00-53) Sunday is the first day of the week
%uWeek (00-53) Monday is the first day of the week
%VWeek (01-53) Sunday is the first day of the week, used with %X
%vWeek (01-53) Monday is the first day of the week, used with %x
%WWeekday name
%wDay of the week (0=Sunday, 6=Saturday)
%XYear, where Sunday is the first day of the week, 4 digits, used with %V
%xYear, where Monday is the first day of the week, 4 digits, used with %v
%YYears, 4 digits
%yYear, 2 digits

2. FROM_UNIXTIME()

Returns a string representing the Unix time stamp, formatted according to the format string. format can contain the same modifiers as those listed in the DATE_FORMAT() function.

Format the date value according to the format string.
The following modifiers can be used in the format string:

  • Display the timestamp stored in int in MySQL in " YYYY-MM-DD " format
  • Syntax: FROM_UNIXTIME(unix_timestamp, format)
    unix_timestamp: the timestamp to be converted
    format: specify the date/time output format. The format that can be used is basically the same as that of the DATE_FORMAT() function, and will not be listed here
  • Example
# 1. 正常查询update_time
SELECT update_time FROM users LIMIT 1;

# 2. 格式化输出update_time
SELECT FROM_UNIXTIME(update_time) FROM users LIMIT 1;

# 3. 自定义格式输出update_time
SELECT FROM_UNIXTIME(update_time,'%Y-%m-%d') FROM users LIMIT 1;

# 4. 查询所有列,格式化输出update_time
SELECT *,FROM_UNIXTIME(update_time) FROM users LIMIT 1;
Query result 1


Query result 2
Search result 3

3. UNIX_TIMESTAMP()

  • Convert data stored as dates in MySQL into timestamps
  • Syntax: UNIX_TIMESTAMP(date)
    date: legal date