MySQL年月日格式化|极客笔记

MySQL年月日格式化

在MySQL数据库中，有时候我们需要将日期数据进行格式化操作，以便更好地满足业务的需求。本文将详细介绍如何在MySQL中对日期数据进行年月日格式化操作。

日期数据类型

在MySQL中，日期数据类型主要有DATE、DATETIME和TIMESTAMP三种。这些数据类型存储日期和时间信息，但它们在存储和表示方式上有所不同。

DATE：仅存储日期部分，格式为YYYY-MM-DD，例如2022-05-20。
DATETIME：存储日期和时间部分，格式为YYYY-MM-DD HH:MM:SS，例如2022-05-20 15:30:45。
TIMESTAMP：存储日期和时间部分，格式为YYYY-MM-DD HH:MM:SS，最大范围到2038-01-19 03:14:07。

日期格式化函数

MySQL提供了一些日期格式化函数，可以将日期数据按照指定的格式进行转换。常用的日期格式化函数有：

DATE_FORMAT(date, format)：将日期格式化为指定格式的字符串。
YEAR(date)：提取日期中的年份部分。
MONTH(date)：提取日期中的月份部分。
DAY(date)：提取日期中的日份部分。
DATE(date)：获取日期部分。
TIME(time)：获取时间部分。

示例

下面通过实际示例来演示如何在MySQL中对日期进行格式化操作。

创建表

首先创建一个名为employees的表，用于存储员工的信息，包括姓名、入职日期等字段。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    hire_date DATE
);

INSERT INTO employees (id, name, hire_date) VALUES
(1, 'Alice', '2022-03-15'),
(2, 'Bob', '2021-07-20'),
(3, 'Charlie', '2020-11-10');

查询所有员工及入职日期

SELECT * FROM employees;

运行结果如下：

+----+---------+------------+
| id | name    | hire_date  |
+----+---------+------------+
|  1 | Alice   | 2022-03-15 |
|  2 | Bob     | 2021-07-20 |
|  3 | Charlie | 2020-11-10 |
+----+---------+------------+

格式化日期为年月日

假设我们想要将入职日期显示为YYYY年MM月DD日的格式，可以使用DATE_FORMAT函数。

SELECT name, DATE_FORMAT(hire_date, '%Y年%m月%d日') AS formatted_hire_date
FROM employees;

运行结果如下：

+---------+---------------------+
| name    | formatted_hire_date |
+---------+---------------------+
| Alice   | 2022年03月15日      |
| Bob     | 2021年07月20日      |
| Charlie | 2020年11月10日      |
+---------+---------------------+

提取年份、月份、日期

如果我们想要分别提取入职日期的年份、月份、日期部分，可以使用YEAR、MONTH、DAY函数。

SELECT name, YEAR(hire_date) AS hire_year, MONTH(hire_date) AS hire_month, DAY(hire_date) AS hire_day
FROM employees;

运行结果如下：

+---------+----------+------------+---------+
| name    | hire_year| hire_month | hire_day|
+---------+----------+------------+---------+
| Alice   | 2022     | 3          | 15      |
| Bob     | 2021     | 7          | 20      |
| Charlie | 2020     | 11         | 10      |
+---------+----------+------------+---------+