MySQL中的日期时间类型与格式化方式

在MySQL中，日期和时间类型是非常重要的数据类型，它们用于存储日期、时间或日期时间信息。MySQL提供了多种日期时间类型，每种类型都有其特定的用途和存储格式。本文将详细介绍MySQL中的日期时间类型及其格式化方式。

1. MySQL中的日期时间类型

MySQL支持以下几种日期时间类型：

• DATE
• TIME
• DATETIME
• TIMESTAMP
• YEAR

1.1 DATE 类型

DATE 类型用于存储日期值，格式为 YYYY-MM-DD。它不包含时间部分。

• 范围：1000-01-01 到 9999-12-31
• 存储空间：3字节

示例：

CREATE TABLE example (
    id INT PRIMARY KEY,
    event_date DATE
);

INSERT INTO example (id, event_date) VALUES (1, '2023-10-05');

1.2 TIME 类型

TIME 类型用于存储时间值，格式为 HH:MM:SS。它可以表示一天中的时间，也可以表示时间间隔。

• 范围：-838:59:59 到 838:59:59
• 存储空间：3字节

示例：

CREATE TABLE example (
    id INT PRIMARY KEY,
    event_time TIME
);

INSERT INTO example (id, event_time) VALUES (1, '14:30:00');

1.3 DATETIME 类型

DATETIME 类型用于存储日期和时间值，格式为 YYYY-MM-DD HH:MM:SS。

• 范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59
• 存储空间：8字节

示例：

CREATE TABLE example (
    id INT PRIMARY KEY,
    event_datetime DATETIME
);

INSERT INTO example (id, event_datetime) VALUES (1, '2023-10-05 14:30:00');

1.4 TIMESTAMP 类型

TIMESTAMP 类型用于存储日期和时间值，格式为 YYYY-MM-DD HH:MM:SS。与 DATETIME 不同的是，TIMESTAMP 类型的值会自动转换为UTC时间存储，并在检索时转换回当前时区。

• 范围：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
• 存储空间：4字节

示例：

CREATE TABLE example (
    id INT PRIMARY KEY,
    event_timestamp TIMESTAMP
);

INSERT INTO example (id, event_timestamp) VALUES (1, '2023-10-05 14:30:00');

1.5 YEAR 类型

YEAR 类型用于存储年份值，格式为 YYYY。

• 范围：1901 到 2155
• 存储空间：1字节

示例：

CREATE TABLE example (
    id INT PRIMARY KEY,
    event_year YEAR
);

INSERT INTO example (id, event_year) VALUES (1, '2023');

2. 日期时间类型的格式化

MySQL提供了多种函数来格式化日期和时间值。常用的格式化函数包括 DATE_FORMAT()、TIME_FORMAT() 和 STR_TO_DATE()。

2.1 DATE_FORMAT() 函数

DATE_FORMAT() 函数用于将日期或日期时间值格式化为指定的字符串格式。

语法：

DATE_FORMAT(date, format)

常用格式符： - %Y：四位数的年份 - %y：两位数的年份 - %m：两位数的月份（01-12） - %d：两位数的日期（01-31） - %H：两位数的小时（00-23） - %i：两位数的分钟（00-59） - %s：两位数的秒数（00-59） - %W：星期几的全名（Sunday-Saturday） - %a：星期几的缩写（Sun-Sat） - %b：月份的缩写（Jan-Dec） - %M：月份的全名（January-December）

示例：

SELECT DATE_FORMAT('2023-10-05 14:30:00', '%Y-%m-%d %H:%i:%s') AS formatted_date;
-- 输出：2023-10-05 14:30:00

SELECT DATE_FORMAT('2023-10-05 14:30:00', '%W, %M %d, %Y') AS formatted_date;
-- 输出：Thursday, October 05, 2023

2.2 TIME_FORMAT() 函数

TIME_FORMAT() 函数用于将时间值格式化为指定的字符串格式。

语法：

TIME_FORMAT(time, format)

常用格式符： - %H：两位数的小时（00-23） - %i：两位数的分钟（00-59） - %s：两位数的秒数（00-59） - %p：AM 或 PM

示例：

SELECT TIME_FORMAT('14:30:00', '%H:%i:%s') AS formatted_time;
-- 输出：14:30:00

SELECT TIME_FORMAT('14:30:00', '%h:%i %p') AS formatted_time;
-- 输出：02:30 PM

2.3 STR_TO_DATE() 函数

STR_TO_DATE() 函数用于将字符串转换为日期或日期时间值。

语法：

STR_TO_DATE(str, format)

常用格式符： - %Y：四位数的年份 - %y：两位数的年份 - %m：两位数的月份（01-12） - %d：两位数的日期（01-31） - %H：两位数的小时（00-23） - %i：两位数的分钟（00-59） - %s：两位数的秒数（00-59）

示例：

SELECT STR_TO_DATE('2023-10-05 14:30:00', '%Y-%m-%d %H:%i:%s') AS date_time;
-- 输出：2023-10-05 14:30:00

SELECT STR_TO_DATE('05/10/2023', '%d/%m/%Y') AS date;
-- 输出：2023-10-05

3. 日期时间类型的计算

MySQL提供了多种函数来进行日期和时间的计算，常用的函数包括 DATE_ADD()、DATE_SUB()、DATEDIFF() 和 TIMESTAMPDIFF()。

3.1 DATE_ADD() 函数

DATE_ADD() 函数用于在日期或日期时间值上添加指定的时间间隔。

语法：

DATE_ADD(date, INTERVAL expr unit)

常用单位： - YEAR：年 - MONTH：月 - DAY：日 - HOUR：小时 - MINUTE：分钟 - SECOND：秒

示例：

SELECT DATE_ADD('2023-10-05', INTERVAL 1 DAY) AS new_date;
-- 输出：2023-10-06

SELECT DATE_ADD('2023-10-05 14:30:00', INTERVAL 2 HOUR) AS new_datetime;
-- 输出：2023-10-05 16:30:00

3.2 DATE_SUB() 函数

DATE_SUB() 函数用于在日期或日期时间值上减去指定的时间间隔。

语法：

DATE_SUB(date, INTERVAL expr unit)

常用单位： - YEAR：年 - MONTH：月 - DAY：日 - HOUR：小时 - MINUTE：分钟 - SECOND：秒

示例：

SELECT DATE_SUB('2023-10-05', INTERVAL 1 DAY) AS new_date;
-- 输出：2023-10-04

SELECT DATE_SUB('2023-10-05 14:30:00', INTERVAL 2 HOUR) AS new_datetime;
-- 输出：2023-10-05 12:30:00

3.3 DATEDIFF() 函数

DATEDIFF() 函数用于计算两个日期之间的天数差。

语法：

DATEDIFF(date1, date2)

示例：

SELECT DATEDIFF('2023-10-05', '2023-10-01') AS days_diff;
-- 输出：4

3.4 TIMESTAMPDIFF() 函数

TIMESTAMPDIFF() 函数用于计算两个日期或日期时间值之间的时间差，可以指定单位。

语法：

TIMESTAMPDIFF(unit, datetime1, datetime2)

常用单位： - YEAR：年 - MONTH：月 - DAY：日 - HOUR：小时 - MINUTE：分钟 - SECOND：秒

示例：

SELECT TIMESTAMPDIFF(DAY, '2023-10-01', '2023-10-05') AS days_diff;
-- 输出：4

SELECT TIMESTAMPDIFF(HOUR, '2023-10-05 12:00:00', '2023-10-05 14:30:00') AS hours_diff;
-- 输出：2

4. 总结

MySQL中的日期时间类型和格式化方式非常丰富，能够满足各种日期和时间处理的需求。通过合理使用这些类型和函数，可以有效地管理和操作数据库中的日期时间数据。无论是存储、格式化还是计算，MySQL都提供了强大的工具来简化开发者的工作。