「MySQL」日期时间格式化函数 DATE_FORMAT() 的使用详解

一、DATE_FORMAT()语法

DATE_FORMAT() 是MySQL中用于格式化日期时间的函数：

语法：DATE_FORMAT(date, format_string)

• date：需要格式化的日期化时间值，一般是需要被格式化的日期时间类型(datetime类)，但也可以是日期时间形式的字符串
• format_string：格式化字符串，用于指定日期时间的格式化输出形式

注1：MySQL允许你对具体的日期时间格式的字符串进行格式化，但其他数据库的格式化函数不一定支持，比如Clickhouse的formatDateTime()
注2：MySQL对字符串进行格式化时，日期时间的格式并不固定，你可以用很多特殊字符隔开日期时间，比如: / * - <等，但不能是空格，MySQL会按照YY MM DD HH MM SS的顺序识别字符串中的数字

-- 比如下列SQl语句的执行结果相同，都是 "2024,December,31st"
SELECT DATE_FORMAT( '24-12-31', '%Y,%M,%D') AS format_date
SELECT DATE_FORMAT( '2024-12-31', '%Y,%M,%D') AS format_date
SELECT DATE_FORMAT( '2024/12/31', '%Y,%M,%D') AS format_date
SELECT DATE_FORMAT( '2024*12*31', '%Y,%M,%D') AS format_date
SELECT DATE_FORMAT( '24<<12<<31', '%Y,%M,%D') AS format_date
-- 但如果使用空格间隔，会执行失败，但不会报错，输出结果为NULL
SELECT DATE_FORMAT( '24 12 31', '%Y,%M,%D') AS format_date

二、格式化字符串详解

格式	含义
%Y	年份：4位数字表示，eg: 2024
%y	年份：2位数字表示，eg: 24
%M	月份：英文全拼表示，eg: June
%m	月份：2位数字表示，范围01-12，eg: 06
%D	天数：两位数字表示，范围01-31，eg: 15
%d	天数：英文第几天表示，范围 1st-31th，eg: 1st, 2nd, 3rd, 15th
%H	小时：两位数字表示，范围00-23，eg: 23
%i	分钟：两位数字表示，范围00-59，eg: 59
%S OR %s	秒钟：两位数字表示，范围00-59，eg: 16
%W	星期：英文全拼表示，eg: Saturday
%w	星期：1位数字表示，eg: 6
%T	完全时间格式，相当于 %H:%i%s 或 %H:%i:%S，范围00:00:00-23:59:59，eg: 15:30:36

注3：大小写形式%I 和%t无意义

三、常见日期时间格式组合

常见的日期时间格式化形式基本上就是如下几种：
%Y,%M,%D
%Y-%m-%d %H:%i:%s
%Y/%m/%d
%W,%M,%D

• SQL语句

SELECT '%Y-%m-%d %H:%i:%s' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%Y-%m-%d %H:%i:%s') AS format_date UNION ALL
SELECT '%Y,%M,%D' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%Y,%M,%D') AS format_date UNION ALL
SELECT '%W,%M,%D' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%W,%M,%D') AS format_date UNION ALL
SELECT '%y/%m/%d' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%y/%m/%d') AS format_date UNION ALL
SELECT '%H:%i:%s' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%H:%i:%s') AS format_date UNION ALL
SELECT '%H:%i:%S' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%H:%i:%S') AS format_date UNION ALL
SELECT '%T' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%T') AS format_date UNION ALL
SELECT '%w,%m-%d' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%w,%m-%d') AS format_date UNION ALL
SELECT '------' AS format, '--------------' AS format_date UNION ALL
SELECT '%H:%I:%s' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%H:%I:%s') AS format_date UNION ALL
SELECT '%t' AS format, DATE_FORMAT( '2024-12-31 23:48:59','%t') AS format_date

• 执行结果
  

四、业务场景

在实际的业务应用如报表导出、界面显示、数据分析等，该函数通常用于输出特定格式的日期或时间，当然，DATE_FORMAT()允许你自定义输出格式，只要你定义好格式化字符串即可。

SELECT DATE_FORMAT(startTime,'%Y-%m') AS startTime 
FROM tableA
WHERE column1 = 'option1'

五、总结

DATE_FORMAT()是MySQL中用于格式化输出日期时间的函数，我们可以通过使用该函数满足大多数格式化日期时间的应用场景，本文的示例展示了如何获取当前日期和时间并将其格式化为一个常见的格式，您可以根据需要调整格式字符串来满足您的具体需求。
但需要注意的是，不同的数据库系统可能支持不同的格式字符串，所以在使用时需要参考相应数据库的文档来确定正确的格式字符串。