MySQL 使用 DATE_FORMAT() 和 FROM_UNIXTIME() 格式化时间

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

真诚赞赏,手留余香
赞赏
MySQL
MySQL DATETIME 时间查询和转换
2022-07-11
Web Server,操作系统,运维
Debian11 安装笔记1:编译安装Nginx、Naxsi 和 Njs
2022-08-13
ngtwewy
随机推荐
Node.JS 邮件发送模块 nodemailer
CSS 滚动条样式修改
Nginx 代理 Node.js 静态文件比较大无法正常显示,关键词 proxy_temp Permission denied
nginx location 配置返回文本
Nginx 报错 nginx accept() failed (24: Too many open files)
nodejs 读写 excel 文件
数据库中间表应该如何命名
PHP-FPM 对三种子进程的管理模式
使用N-API 写 Node.js 的 C语言扩展
封装一个像 axios.js 一样的 http 请求拦截器

微信联系我

夜间模式切换
回到顶部