MySQL Convert 日期格式转换

在MySQL中，日期格式的转换是一个常见的需求，特别是在处理和展示不同格式的日期数据时。MySQL提供了一些内置函数，可以帮助我们轻松地进行日期格式的转换。本文将介绍一些常用的MySQL日期格式转换函数，并提供相关的代码示例。

DATE_FORMAT函数

DATE_FORMAT函数是MySQL中最常用的日期格式转换函数之一。它可以将日期数据按照指定的格式进行格式化。

语法：

DATE_FORMAT(date, format)

其中，date是一个日期或日期时间表达式，format是一个表示日期格式的字符串。

下表是常用的日期格式字符串及其对应的含义：

格式字符串 含义
%Y 年份，4位数字
%y 年份，2位数字
%m 月份，两位数字（01-12）
%c 月份，数字（1-12）
%M 月份，全名（January-December）
%b 月份，缩写（Jan-Dec）
%d 日期，两位数字（01-31）
%e 日期，数字（1-31）
%H 小时，24小时制，两位数字（00-23）
%h 小时，12小时制，两位数字（01-12）
%i 分钟，两位数字（00-59）
%s 秒，两位数字（00-59）
%p AM或PM
%w 星期，数字（0-6，0表示Sunday）
%W 星期，全名（Sunday-Saturday）

下面是一个示例，将日期字段create_time格式化为YYYY-MM-DD HH:ii:ss的格式：

SELECT DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS formatted_time FROM my_table;

STR_TO_DATE函数

STR_TO_DATE函数是MySQL中用于将字符串转换为日期的函数。它的语法如下：

STR_TO_DATE(str, format)

其中，str是一个表示日期的字符串，format是一个表示日期格式的字符串。

下面是一个示例，将字符串'2022-12-31'转换为日期类型：

SELECT STR_TO_DATE('2022-12-31', '%Y-%m-%d') AS converted_date;

UNIX_TIMESTAMP函数和FROM_UNIXTIME函数

UNIX_TIMESTAMP函数用于将日期时间转换为UNIX时间戳，而FROM_UNIXTIME函数则用于将UNIX时间戳转换为日期时间。

UNIX时间戳是从1970年1月1日00:00:00起经过的秒数。

下面是一个示例，将日期时间字段create_time转换为UNIX时间戳：

SELECT UNIX_TIMESTAMP(create_time) AS unix_timestamp FROM my_table;

下面是一个示例，将UNIX时间戳1640908800转换为日期时间：

SELECT FROM_UNIXTIME(1640908800) AS formatted_time;

示例

以下是一个完整的示例，假设我们有一个名为users的表，其中包含一个字段birthday表示用户的生日。

CREATE TABLE users (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   birthday DATE NOT NULL
);

INSERT INTO users (name, birthday) VALUES
   ('Alice', '1990-01-01'),
   ('Bob', '1995-02-02'),
   ('Charlie', '2000-03-03');

现在，我们想要从users表中查询生日在2022年之前的用户，并将生日格式化为YYYY年M月D日的形式。

SELECT name, DATE_FORMAT(birthday, '%Y年%c月%d日') AS formatted_birthday
FROM users
WHERE YEAR(birthday) <= 2022;

执行以上查询语句，将返回以下结果：

name formatted_birthday
Alice 1990年1月1日
Bob 1995年2月2日
Charlie 2000年3月3日

状态图

下面是一个使用mermaid语法表示的状态图，展示了日期转换的过程。

stateDiagram
    [*] --> FormatDate
    FormatDate --> ConvertTo