10.4 日期和时间函数-MySQL从入门到精通(三)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(三)

### 10.4 日期和时间函数

在MySQL中，日期和时间函数是处理数据库中时间相关数据的强大工具。无论是进行数据的时间分析、生成报告，还是确保数据的时间一致性，这些函数都扮演着至关重要的角色。本章节将详细介绍MySQL中常用的日期和时间函数，包括日期提取、时间计算、日期格式化等各个方面，帮助读者从入门到精通MySQL中的日期和时间处理技巧。

#### 10.4.1 日期和时间基础

在深入探讨函数之前，先简要回顾一下MySQL中日期和时间的表示方法。MySQL支持多种日期和时间类型，包括`DATE`（仅日期）、`TIME`（仅时间）、`DATETIME`（日期和时间）、`TIMESTAMP`（时间戳，具有时区特性）、`YEAR`（年份）等。每种类型都有其特定的格式和应用场景。

#### 10.4.2 当前日期和时间

- **NOW()**：返回当前的日期和时间，格式为`'YYYY-MM-DD HH:MM:SS'`或`'YYYYMMDDHHMMSS'`，具体取决于函数的调用方式（是否带有参数）或服务器的SQL模式。
- **CURDATE()**：仅返回当前日期，格式为`'YYYY-MM-DD'`。
- **CURTIME()**：仅返回当前时间，格式为`'HH:MM:SS'`。

#### 10.4.3 日期和时间提取

MySQL提供了一系列函数来从日期或时间值中提取特定的部分，如年、月、日、小时等。

- **YEAR(date)**：从日期或日期时间表达式中提取年份。
- **MONTH(date)**：提取月份。
- **DAY(date)**：提取日。
- **HOUR(time)**：从时间或日期时间表达式中提取小时。
- **MINUTE(time)**：提取分钟。
- **SECOND(time)**：提取秒。
- **DAYOFWEEK(date)**：返回日期是周几（1=周日，2=周一，...，7=周六）。
- **DAYOFYEAR(date)**：返回日期是一年中的第几天（1-366）。

#### 10.4.4 日期和时间计算

MySQL允许通过加减操作对日期和时间进行简单的计算，同时也提供了专门的函数来处理更复杂的日期时间运算。

- **DATE_ADD(date, INTERVAL expr type)**：在日期上加上一个时间间隔。`expr`是你要加上的时间量，`type`是间隔的单位（如`DAY`、`MONTH`、`YEAR`等）。
- **DATE_SUB(date, INTERVAL expr type)**：从日期上减去一个时间间隔，与`DATE_ADD`相反。
- **ADDDATE(date, INTERVAL expr type) / SUBDATE(date, INTERVAL expr type)**：`ADDDATE`和`SUBDATE`是`DATE_ADD`和`DATE_SUB`的同义词，功能上完全一致。
- **DATEDIFF(expr1, expr2)**：计算两个日期之间的天数差。
- **TIMEDIFF(expr1, expr2)**：计算两个时间或日期时间值之间的差异，结果是一个时间值。

#### 10.4.5 日期和时间格式化

格式化日期和时间输出是常见的需求，MySQL的`DATE_FORMAT()`函数为此提供了强大的支持。

- **DATE_FORMAT(date, format)**：根据指定的格式字符串`format`来格式化日期或时间值。`format`字符串中可以包含各种格式化代码，如`%Y`代表四位年份，`%m`代表月份，`%d`代表日，`%H`代表小时（24小时制），`%i`代表分钟，`%s`代表秒等。

#### 10.4.6 时间戳函数

时间戳是自1970年1月1日（称为Unix纪元）以来的秒数（或毫秒数，取决于具体的函数和系统）。MySQL中的`UNIX_TIMESTAMP()`函数和`FROM_UNIXTIME()`函数用于处理时间戳。

- **UNIX_TIMESTAMP()**：无参数时返回当前时间的UNIX时间戳；带日期参数时，返回该日期的UNIX时间戳。
- **FROM_UNIXTIME(unix_timestamp)**：将UNIX时间戳转换为`'YYYY-MM-DD HH:MM:SS'`格式的日期时间值。

#### 10.4.7 实战应用

理解上述函数后，我们来看几个实际的应用场景。

- **场景一：计算员工入职天数**：假设有一个员工表`employees`，包含字段`hire_date`（入职日期），可以使用`DATEDIFF(CURDATE(), hire_date)`来计算每个员工的入职天数。
- **场景二：格式化订单日期**：在订单系统中，可能需要将订单日期按照特定格式（如`YYYY年MM月DD日`）展示给用户，这时可以使用`DATE_FORMAT(order_date, '%Y年%m月%d日')`来实现。
- **场景三：计算下个月的第一个星期一**：这可能需要结合多个函数来实现，如先使用`DATE_ADD()`加上一个月，然后使用`WEEKDAY()`函数和条件逻辑来找到第一个星期一。

#### 10.4.8 注意事项

- 在使用日期和时间函数时，务必注意输入值的类型和格式，确保它们与函数要求的参数类型相匹配。
- 考虑到时区的影响，特别是在处理`TIMESTAMP`类型的数据时，需要了解并正确设置MySQL服务器的时区设置，以避免因时区差异导致的数据不一致问题。
- 在进行日期时间计算时，要注意不同月份的天数差异（如2月可能只有28天或29天）以及闰年的情况。

通过本章节的学习，读者应该能够熟练掌握MySQL中日期和时间函数的使用，并能够灵活应用到实际的数据处理和分析工作中。无论是简单的日期提取和格式化，还是复杂的日期时间计算和转换，都能得心应手。

该分类下的相关小册推荐：

MySQL从入门到精通(五)

MySQL 实战 45 讲

MySQL从入门到精通(二)

MySQL8.0入门与实践

SQL零基础到熟练应用(增删改查)

MySQL从入门到精通(一)

MySQL必会核心问题

MySQL必知必会核心内容

细说MySQL(零基础到高级应用)

MySQL从入门到精通(四)