DATE_FORMAT(date, format)

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

format 参数的格式有：

%a	Everyday name in Shortened form [Sun–Sat].
%b	Month name in Shortened form [Jan – Dec].
%c	Name of Month in numbers [0 – 12].
%D	Daytime of the month in a numeric form, trailed by suffix [like 1st, 2nd, …].
%d	Daytime of the month as a numeric form[01 – 31].
%e	Daytime of the month as a numeric form [0 – 31].
%f	Denotes Microseconds within range [000000 – 999999].
%H	Denotes Hour within range[00 – 23].
%h	Denotes Hour within range [00 – 12].
%I	Denotes Hour within range [00 – 12].
%i	Numeric Minutes ranging [00 – 59].
%j	Daytime of the year [001 – 366].
%k	Denotes Hour within range [0 – 23].
%l	Denotes Hour within range [1 – 12].
%M	Full name of month [January – December].
%m	Numeric name of Month [00 – 12].
%p	Either AM or PM.
%r	Denotes Time in 12 hour AM or PM format [hh:mm:ss AM/PM].
%S	Denotes Seconds ranging [00 – 59].
%s	Denotes Seconds ranging [00 – 59].
%T	24 hour format of Time [hh:mm:ss].
%U	Denotes Week where Sun is the initial day of the week [00 – 53].
%u	Denotes Week where Mon is the initial day of the week [00 – 53].
%V	Denotes Week where Sun is the initial day of the week [01 – 53] and used also with %X.
%v	Denotes Week where Mon is the initial day of the week [01 – 53] and used also with %X.
%W	Name of Weekday in full [Sunday – Saturday].
%w	Daytime of the week where Sunday=0 and Saturday=6.
%X	Denotes Year for the week where Sun is the initial day of the week and used also with %V.
%x	Denotes Year for the week where Mon is the first day of the week and used also with %V.
%Y	Denotes Year as a numeric, 4-digit value.
%y	Denotes Year as a numeric, 2-digit value.
%%	Supplements percentage (%) character to the result.

例子：

select NOW();

2022-09-23 10:48:25

select DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i %p');

2022-09-23 10:49 AM

select DATE_FORMAT(NOW(),'%Y年%m月%d日 %h:%i:%s');

2022年09月23日 10:51:04

FROM_UNIXTIME(unix_timestamp,format)

把时间戳转换成时间。

例子：

select FROM_UNIXTIME(1663902524443/1000, '%Y年%m月%d日 %h:%i:%s');

2022年09月23日 11:08:44

select FROM_UNIXTIME(1663902524, '%Y年%m月%d日');

2022年09月23日

格式参考：

'%Y-%m-%d'     2011-02-11
'%e/%c/%Y'     11/2/2011 UK
'%c/%e/%Y'     2/11/2011 US
'%d/%m/%Y'     11/02/2011 UK
'%m/%d/%Y'     02/11/2011 US
'%e/%c/%Y %H:%i'     11/2/2011 12:30 UK
'%c/%e/%Y %H:%i'     2/11/2011 12:30 US
'%d/%m/%Y %H:%i'     11/02/2011 12:30 UK
'%m/%d/%Y %H:%i'     02/11/2011 12:30 US
'%e/%c/%Y %T'    11/2/2011 12:30:10 UK
'%c/%e/%Y %T'    2/11/2011 12:30:10 US
'%d/%m/%Y %T'    11/02/2011 12:30:10 UK
'%m/%d/%Y %T'    02/11/2011 12:30:10 US
'%a %D %b %Y'    Fri 11th Feb 2011
'%a %D %b %Y %H:%i'    Fri 11th Feb 2011 12:30
'%a %D %b %Y %T'     Fri 11th Feb 2011 12:30:10
'%a %b %e %Y'    Fri Feb 11 2011
'%a %b %e %Y %H:%i'    Fri Feb 11 2011 12:30
'%a %b %e %Y %T'     Fri Feb 11 2011 12:30:10
'%W %D %M %Y'    Friday 11th February 2011
'%W %D %M %Y %H:%i'    Friday 11th February 2011 12:30
'%W %D %M %Y %T'     Friday 11th February 2011 12:30:10
'%l:%i %p %b %e, %Y'     12:30 PM Feb 11, 2011
'%M %e, %Y'    February 11, 2011

参考：

https://www.educba.com/mysql-date_format/

https://www.w3resource.com/mysql/date-and-time-functions/mysql-date_format-function.php



真诚赞赏，手留余香

微信支付