MySQL第7课：日期类型详解

MySQL提供了丰富的日期和时间类型，用于存储和处理时间相关数据。正确使用这些类型对于任何数据库应用都至关重要。

1. MySQL日期和时间类型概览

MySQL提供了5种主要的日期和时间类型，每种类型有不同的存储需求和适用范围：

数据类型	存储空间	格式	范围	描述
DATE	3字节	YYYY-MM-DD	1000-01-01 到 9999-12-31	日期值
TIME	3字节	HH:MM:SS	-838:59:59 到 838:59:59	时间值
DATETIME	8字节	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 到 9999-12-31 23:59:59	日期和时间值
TIMESTAMP	4字节	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC	时间戳，带时区转换
YEAR	1字节	YYYY	1901 到 2155	年份值

2. 基本用法和示例

创建表时使用日期类型

CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100),
    event_date DATE,                  -- 存储日期
    start_time TIME,                  -- 存储时间
    created_at DATETIME,              -- 存储日期和时间
    updated_at TIMESTAMP,             -- 自动更新的时间戳
    event_year YEAR                   -- 存储年份
);

插入日期数据

-- 插入明确的日期值
INSERT INTO events (event_name, event_date, start_time, created_at, event_year)
VALUES (
    '产品发布会',
    '2023-10-15',                    -- DATE
    '14:30:00',                      -- TIME
    '2023-09-21 10:15:30',           -- DATETIME
    2023                             -- YEAR
);

-- 使用MySQL函数插入当前日期和时间
INSERT INTO events (event_name, event_date, start_time, created_at)
VALUES (
    '团队会议',
    CURDATE(),                       -- 当前日期
    CURTIME(),                       -- 当前时间
    NOW()                            -- 当前日期和时间
);

3. 日期和时间函数

MySQL提供了丰富的日期和时间函数来处理日期数据：

获取当前日期和时间

SELECT NOW();        -- 当前日期和时间，格式: YYYY-MM-DD HH:MM:SS
SELECT CURDATE();    -- 当前日期，格式: YYYY-MM-DD
SELECT CURTIME();    -- 当前时间，格式: HH:MM:SS
SELECT UTC_DATE();   -- 当前UTC日期
SELECT UTC_TIME();   -- 当前UTC时间
SELECT UTC_TIMESTAMP(); -- 当前UTC日期和时间

提取日期部分

SELECT DATE('2023-09-21 14:30:45');      -- 提取日期部分: 2023-09-21
SELECT TIME('2023-09-21 14:30:45');      -- 提取时间部分: 14:30:45
SELECT YEAR('2023-09-21');               -- 提取年份: 2023
SELECT MONTH('2023-09-21');              -- 提取月份: 9
SELECT DAY('2023-09-21');                -- 提取日: 21
SELECT HOUR('14:30:45');                 -- 提取小时: 14
SELECT MINUTE('14:30:45');               -- 提取分钟: 30
SELECT SECOND('14:30:45');               -- 提取秒: 45

日期计算

-- 增加时间间隔
SELECT DATE_ADD('2023-09-21', INTERVAL 7 DAY);      -- 增加7天: 2023-09-28
SELECT DATE_ADD('2023-09-21', INTERVAL 1 MONTH);    -- 增加1个月: 2023-10-21
SELECT DATE_ADD('2023-09-21', INTERVAL 2 YEAR);     -- 增加2年: 2025-09-21

-- 减少时间间隔
SELECT DATE_SUB('2023-09-21', INTERVAL 7 DAY);      -- 减少7天: 2023-09-14

-- 计算日期差
SELECT DATEDIFF('2023-09-28', '2023-09-21');        -- 日期差: 7
SELECT TIMEDIFF('14:30:45', '10:15:30');            -- 时间差: 04:15:15

-- 获取星期几
SELECT DAYNAME('2023-09-21');                       -- Thursday
SELECT DAYOFWEEK('2023-09-21');                     -- 5 (1=Sunday, 2=Monday, ...)

4. 日期格式化

使用DATE_FORMAT函数自定义日期显示格式：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');              -- 2023-09-21
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');         -- Thursday, September 21, 2023
SELECT DATE_FORMAT(NOW(), '%h:%i %p');              -- 02:30 PM

-- 常用格式说明符:
-- %Y - 4位年份 (2023)
-- %y - 2位年份 (23)
-- %m - 月份 (01-12)
-- %c - 月份 (1-12)
-- %M - 月份名称 (January-December)
-- %b - 缩写月份名称 (Jan-Dec)
-- %d - 日 (01-31)
-- %e - 日 (1-31)
-- %H - 小时 (00-23)
-- %h - 小时 (01-12)
-- %I - 小时 (01-12)
-- %i - 分钟 (00-59)
-- %s - 秒 (00-59)
-- %p - AM/PM
-- %W - 星期名称 (Sunday-Saturday)
-- %a - 缩写星期名称 (Sun-Sat)

5. 实际应用示例

示例1：事件管理系统

CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_date DATE NOT NULL,
    end_date DATE,
    start_time TIME,
    end_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入事件
INSERT INTO events (title, description, start_date, end_date, start_time, end_time)
VALUES (
    '年度技术会议',
    '讨论最新技术趋势和创新',
    '2023-11-15',
    '2023-11-17',
    '09:00:00',
    '17:00:00'
);

-- 查询今天的事件
SELECT * FROM events WHERE start_date = CURDATE();

-- 查询未来7天内的事件
SELECT * FROM events WHERE start_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

-- 查询上午的事件
SELECT * FROM events WHERE HOUR(start_time) < 12;

示例2：用户活动日志

CREATE TABLE user_activity (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type VARCHAR(50) NOT NULL,
    activity_details TEXT,
    activity_time DATETIME NOT NULL,
    INDEX idx_user_activity (user_id, activity_time)
);

-- 插入活动记录
INSERT INTO user_activity (user_id, activity_type, activity_details, activity_time)
VALUES 
(1, 'login', '用户登录系统', NOW()),
(1, 'view_page', '查看产品页面', DATE_ADD(NOW(), INTERVAL 5 MINUTE)),
(1, 'purchase', '购买产品A', DATE_ADD(NOW(), INTERVAL 15 MINUTE));

-- 查询用户最近活动
SELECT * FROM user_activity 
WHERE user_id = 1 
ORDER BY activity_time DESC 
LIMIT 10;

-- 查询今日活跃用户
SELECT DISTINCT user_id FROM user_activity 
WHERE DATE(activity_time) = CURDATE();

示例3：订单管理系统

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_time TIME NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入订单
INSERT INTO orders (customer_id, order_date, order_time, total_amount)
VALUES 
(101, CURDATE(), CURTIME(), 149.99),
(102, CURDATE(), DATE_ADD(CURTIME(), INTERVAL 30 MINUTE), 299.50);

-- 查询今日订单总额
SELECT SUM(total_amount) AS daily_total FROM orders 
WHERE order_date = CURDATE();

-- 查询本月订单统计
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue
FROM orders 
WHERE order_date >= DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

6. 自动更新时间戳

MySQL可以自动设置和更新时间戳字段：

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入时，created_at和updated_at都会设置为当前时间
INSERT INTO articles (title, content) VALUES ('MySQL指南', '内容...');

-- 更新时，updated_at会自动更新为当前时间
UPDATE articles SET content = '更新后的内容' WHERE id = 1;

7. 日期比较和过滤

-- 比较日期
SELECT * FROM events WHERE start_date > '2023-10-01';

-- 使用BETWEEN查询日期范围
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-09-01' AND '2023-09-30';

-- 查询特定月份的数据
SELECT * FROM events 
WHERE YEAR(start_date) = 2023 AND MONTH(start_date) = 10;

-- 查询最近30天的数据
SELECT * FROM user_activity 
WHERE activity_time >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 查询今天生日的用户
SELECT * FROM users 
WHERE MONTH(birthdate) = MONTH(CURDATE()) 
AND DAY(birthdate) = DAY(CURDATE());

8. 处理时区问题

MySQL提供时区相关函数处理跨时区应用：

-- 设置会话时区
SET time_zone = '+08:00';  -- 设置为东八区(北京时间)

-- 转换时区
SELECT 
    CONVERT_TZ('2023-09-21 10:00:00', '+00:00', '+08:00') AS beijing_time,
    CONVERT_TZ('2023-09-21 10:00:00', '+00:00', '-05:00') AS new_york_time;

-- 获取时区偏移量
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP()) AS timezone_offset;

9. 最佳实践和注意事项

选择合适的数据类型：
- 只需要日期：使用DATE
- 只需要时间：使用TIME
- 需要日期和时间：使用DATETIME或TIMESTAMP
- 需要自动更新时间戳：使用TIMESTAMP
使用标准格式：始终使用YYYY-MM-DD格式存储日期，避免歧义
注意TIMESTAMP范围：TIMESTAMP只能存储到2038年，长期项目考虑使用DATETIME
考虑时区：如果应用涉及多个时区，使用CONVERT_TZ函数或存储UTC时间

使用索引：对经常用于查询的日期字段创建索引以提高性能

CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_activity_time ON user_activity(activity_time);

避免函数操作索引字段：在WHERE子句中避免对索引字段使用函数，这会阻止索引使用

-- 不好：阻止索引使用
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- 好：可以使用索引
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

10. 练习项目

项目：创建个人任务管理系统

CREATE TABLE tasks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    due_date DATE,
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    status ENUM('pending', 'in progress', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入示例任务
INSERT INTO tasks (title, description, due_date, priority) VALUES
('学习MySQL日期类型', '掌握日期和时间函数的使用', '2023-09-25', 'high'),
('准备会议材料', '准备下周会议的演示文稿', '2023-09-28', 'medium'),
('完成项目报告', '编写项目总结报告', '2023-10-05', 'high');

-- 查询即将到期的任务
SELECT * FROM tasks 
WHERE due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
AND status != 'completed'
ORDER BY priority DESC, due_date ASC;

-- 更新任务状态
UPDATE tasks SET status = 'in progress' 
WHERE id = 1 AND status = 'pending';

-- 查询每月任务统计
SELECT 
    DATE_FORMAT(due_date, '%Y-%m') AS month,
    COUNT(*) AS total_tasks,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_tasks
FROM tasks 
GROUP BY DATE_FORMAT(due_date, '%Y-%m')
ORDER BY month;

总结

MySQL提供了多种日期和时间类型，每种适用于不同的场景
使用日期和时间函数可以方便地处理和计算日期
TIMESTAMP类型适合自动记录创建和更新时间
注意时区问题，特别是在跨时区应用中
对经常查询的日期字段创建索引以提高性能
避免在WHERE子句中对索引字段使用函数

实践建议：创建一些包含日期字段的表，练习插入、查询和更新日期数据。尝试使用不同的日期函数解决实际问题，如计算年龄、工作日差异、生成报告等。

通过实际应用这些日期类型和函数，你会逐渐掌握MySQL中日期和时间处理的强大功能。

菜单

分享

MySQL第7课：日期类型详解

1. MySQL日期和时间类型概览

2. 基本用法和示例

创建表时使用日期类型

插入日期数据

3. 日期和时间函数

获取当前日期和时间

提取日期部分

日期计算

4. 日期格式化

5. 实际应用示例

示例1：事件管理系统

示例2：用户活动日志

示例3：订单管理系统

6. 自动更新时间戳

7. 日期比较和过滤

8. 处理时区问题

9. 最佳实践和注意事项

10. 练习项目

项目：创建个人任务管理系统

总结

评论

MySQL项目：学生成绩表

C#6.1 一维数组

MySQL第2课：创建数据表(DDL)

C#6.2 二维数组

C#4.2：IF分支语句练习题

C#6.4.1 数组属性与方法

MySQL练习题: SELECT比较和逻辑运算符

C#4.3练习题: While循环结构语句

C#4.4：跳转语句用法详解

C#6.4.5二分查找法