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
声明:本站所有文章和图片,如无特殊说明,均为原创发布,转载请注明出处。