MySQL 日期转换|极客笔记

MySQL 日期转换

在MySQL数据库中，日期和时间是一种常见的数据类型。在很多情况下，我们需要对日期进行转换，比如将日期格式从一个形式转换为另一个形式，或者提取日期中的特定部分。在本文中，我们将详细介绍在MySQL中如何进行日期转换操作。

日期数据类型

在MySQL中，日期和时间可以使用以下几种数据类型进行存储：

DATE：存储日期，格式为 ‘YYYY-MM-DD’
TIME：存储时间，格式为 ‘HH:MM:SS’
DATETIME：同时存储日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’
TIMESTAMP：存储日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’，并自动维护更新时间
YEAR：存储年份，格式为 ‘YYYY’

MySQL日期格式转换

MySQL提供了一系列函数来进行日期格式的转换，下面是一些常用的日期函数：

DATE_FORMAT(date, format)：将日期字段按指定格式输出
STR_TO_DATE(date, format)：将字符串转换为日期
YEAR(date)、MONTH(date)、DAY(date)：提取日期中的年、月、日
CURDATE()、CURTIME()：获取当前日期和时间
NOW()：获取当前日期和时间

下面我们将分别介绍这些函数的用法：

DATE_FORMAT

DATE_FORMAT函数用于将日期字段按照指定的格式输出，格式由格式化字符串表示。常见的格式化字符串有：

%Y：四位年份
%m：两位月份
%d：两位日期
%H：24小时制的小时
%i：分钟
%s：秒

示例：

SELECT DATE_FORMAT('2022-12-25', '%Y-%m-%d');

输出为：’2022-12-25′

STR_TO_DATE

STR_TO_DATE函数用于将字符串转换为日期，可以指定字符串的格式。示例：

SELECT STR_TO_DATE('2022-12-25', '%Y-%m-%d');

输出为：’2022-12-25′

YEAR、MONTH、DAY

YEAR、MONTH、DAY函数用于提取日期中的年、月、日。示例：

SELECT YEAR('2022-12-25'), MONTH('2022-12-25'), DAY('2022-12-25');

输出为：’2022′, ’12’, ’25’

CURDATE、CURTIME、NOW

CURDATE、CURTIME、NOW函数用于获取当前的日期和时间。示例：

SELECT CURDATE(), CURTIME(), NOW();

输出为当前的日期和时间。

日期计算

除了日期格式的转换，我们还可以在MySQL中进行日期的计算。下面是一些常见的日期计算函数：

DATE_ADD(date, INTERVAL value unit)：给日期字段加上一个时间间隔
DATE_SUB(date, INTERVAL value unit)：从日期字段中减去一个时间间隔
DATEDIFF(date1, date2)：计算两个日期之间的天数差
DATE_FORMAT()：格式化日期
TIMESTAMPDIFF(unit, date1, date2)：计算两个日期之间的时间差

下面我们将分别介绍这些函数的用法：

DATE_ADD、DATE_SUB

DATE_ADD和DATE_SUB函数分别用于给日期字段加上或减去一个时间间隔。时间间隔由value和unit组成，比如1 DAY、1 MONTH等。示例：

SELECT DATE_ADD('2022-12-25', INTERVAL 1 DAY), DATE_SUB('2022-12-25', INTERVAL 1 MONTH);

DATEDIFF

DATEDIFF函数用于计算两个日期之间的天数差。示例：

SELECT DATEDIFF('2022-12-25', '2022-12-01');

输出为：’24’

TIMESTAMPDIFF

TIMESTAMPDIFF函数用于计算两个日期之间的时间差，单位可以是SECOND、MINUTE、HOUR、DAY等。示例：

SELECT TIMESTAMPDIFF(DAY, '2022-12-01', '2022-12-25');

输出为：’24’

日期转换实例

下面我们将通过一个具体的实例来演示如何在MySQL中进行日期格式的转换和计算：

假设我们有一个订单表order_info，字段包括order_id、order_date等。现在我们需要查询出订单日期，格式为’YYYY年MM月DD日’，并计算订单日期距离当前日期的天数。

CREATE TABLE order_info (
    order_id INT,
    order_date DATE
);

INSERT INTO order_info (order_id, order_date) VALUES
(1, '2022-12-25'),
(2, '2022-12-01'),
(3, '2022-11-15');

SELECT order_id, DATE_FORMAT(order_date, '%Y年%m月%d日') AS formatted_date,
DATEDIFF(CURDATE(), order_date) AS days_diff
FROM order_info;

输出为：