--- theme: orange --- ## 引言 > 玄幻仙侠文章都说时间为尊，空间为王；在数据库中，时间是非常非常重要的一种数据类型，合理的存储时间，会让我们系统体验更加舒适，同样，掌握好时间相关的函数，也是我们开发中必不可少的一环。 ## 一、MySQL时间函数概览 MySQL提供了一组内置的时间函数，用于处理和操作日期和时间数据。这些时间函数可以在查询中使用，以执行各种与时间相关的操作。 MySQL时间函数的主要有以下作用： ```mermaid %%{init: {'theme':'base', 'themeVariables': {'lineColor':'#ee712e'}}}%% graph LR A(时间函数的作用) B(获取当前时间) C(格式化时间) D(提取日期和时间部分) E(计算日期和时间间隔) F(添加和减去时间间隔) G(时间转换) A ---> B A ---> C A ---> D A ---> E A ---> F A ---> G style A fill:#ee712e,stroke:#ee712e,stroke-width:2px,color:#FFFFFF style B fill:#FFFFFF,stroke:#ee712e,stroke-width:2px style C fill:#FFFFFF,stroke:#f6bca9,stroke-width:2px style D fill:#FFFFFF,stroke:#ee712e,stroke-width:2px style E fill:#FFFFFF,stroke:#f6bca9,stroke-width:2px style F fill:#FFFFFF,stroke:#ee712e,stroke-width:2px style G fill:#FFFFFF,stroke:#f6bca9,stroke-width:2px ``` ## 二、日期和时间的表示方法 ### 2.1 MySQL中日期和时间的存储格式在MySQL中，日期和时间可以使用多种存储格式来表示。以下是MySQL中常用的日期和时间存储格式： | 数据类型 | 存储格式 | 示例 | 中文说明 | | -------------------------------------------------- | -------------------------------------------------- | ------------------------------------------------ | -------------------------------------------------- | | DATE | YYYY-MM-DD | 2021-09-15 | 日期，表示年、月、日 | | TIME | HH:MM:SS | 09:30:45 | 时间，表示小时、分钟、秒 | | DATETIME | YYYY-MM-DD HH:MM:SS | 2021-09-15 09:30:45 | 日期和时间的组合，表示年、月、日、小时、分钟、秒 | | TIMESTAMP | YYYY-MM-DD HH:MM:SS | 2021-09-15 09:30:45 | 时间戳，表示从1970年1月1日到2038年1月19日的时间范围 | | YEAR | YYYY | 2021 | 年份，表示年份值 | ### 2.2 如何正确输入不同类型的日期和时间值在MySQL中，不同类型的日期和时间，需要输入不同的值，我们必须掌握正确的输入方法，才能保证程序的健壮性，下面我们具体看看不同的类型如何输入： - DATE：日期值可以使用'YYYY-MM-DD'的格式输入。 - 例如，要插入2024年3月15日，可以使用以下语句： ```sql INSERT INTO test_date (date_column) VALUES ('2024-03-15'); ``` - TIME：时间值可以使用'HH:MM:SS'的格式输入。 - 例如，要插入上午17点58分45秒的时间，可以使用以下语句： ```sql INSERT INTO test_time (time_column) VALUES ('17:58:45'); ``` - DATETIME：日期和时间值可以使用'YYYY-MM-DD HH:MM:SS'的格式输入。 - 例如，要插入2024年3月15日上午17点58分45秒的日期和时间，可以使用以下语句： ```sql INSERT INTO test_datetime (datetime_column) VALUES ('2024-03-15 17:58:45'); ``` - TIMESTAMP：对于时区敏感的时间戳，可以使用'YYYY-MM-DD HH:MM:SS'的格式输入。MySQL会自动将其转换为UTC时间进行存储。 - 例如，要插入当前日期和时间作为时间戳，可以使用以下语句： ```sql -- 获取系统当前的时间戳 INSERT INTO test_timestamp (timestamp_column) VALUES (CURRENT_TIMESTAMP); -- 转换代码获取的时间戳 INSERT INTO test_timestamp (timestamp_column) VALUES (FROM_UNIXTIME(1710496924)); ``` 反例： ```sql INSERT INTO test_timestamp (timestamp_column) VALUES (1710496924389); -- 上面插入的方法将得到一个0000-00-00 00:00:00的错误值 ``` - YEAR：年份值应使用'YYYY'的格式输入。 - 例如，要插入2024年的年份值，可以使用以下语句： ```sql INSERT INTO test_year (year_column) VALUES (2024); ``` ## 三、常用的MySQL时间函数 ### 3.1 当前时间的获取 ![Pasted image 20240315181841.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/834b21ffb7cc4fa8b95c0767bbbc9b4f~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=840&h=528&s=64121&e=png&b=fffefe) ### 3.2 日期和时间的提取在MySQL中，可以使用函数来提取日期和时间的特定部分，这些函数可以根据您的需要灵活运用，以提取日期和时间的特定部分或按照特定的格式进行格式化。日期和时间的提取函数可以的分为`简单提取`和`复杂提取`，具体提取函数使用语法和效果如下图所示： ![日期和时间的提取.jpg](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/53faedaaad304e329ff7bff8609a5bb6~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=2108&h=2346&s=298415&e=jpg&b=fefcfc) ### 3.3 日期和时间的转换 MySQL提供了一些函数来进行日期和时间的转换，方便能够在不同的格式之间进行转换。 - 以下是一些常用的日期和时间转换函数： ![日期和时间的转化函数.jpg](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/1cc68d3f4fe147159cc13c33e5cee038~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=2260&h=1316&s=216673&e=jpg&b=fffdfd) - MySQL 中还有用于将日期和时间从一个时区转换到另一个时区的函数 `CONVERT_TZ(datetime, from_tz, to_tz)` - `datetime`：要转换的日期时间值。 - `from_tz`：要转换的日期时间值的当前时区。 - `to_tz`：要转换为的目标时区。使用案例： ```sql SELECT CONVERT_TZ('2024-03-14 23:30:00', 'Asia/Shanghai', 'America/New_York'); -- 执行结果：2024-03-14 11:30:00 ``` > 注意：要使用`CONVERT_TZ(datetime, from_tz, to_tz)`函数，需要保证mysql的时区设置、时区数据和函数参数，确保它们都正确无误。 ### 3.4 计算时间差 MySQL提供了几个函数用于计算日期和时间之间的差异，具体情况如下： ![Snipaste_2024-03-16_21-10-18.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/481b7498e2ee48d4849bc423d73032e9~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1014&h=502&s=55743&e=png&b=ffffff) > 注意，在使用这些函数时，确保日期和时间的格式正确，并根据需要进行适当的转换和处理。 ### 3.5 时间戳和日期的转换在MySQL中，提供了两种函数进行时间戳和日期的转换，具体使用方法如下： - `FROM_UNIXTIME(unix_timestamp, [format])`：将UNIX时间戳转换为日期时间格式。可选择性地指定输出的日期时间格式。示例： ```sql SELECT FROM_UNIXTIME(1631699445); -- 2024-03-16 08:15:50 SELECT FROM_UNIXTIME(1631699445, '%Y-%m-%d %H:%i:%s'); -- 2024-03-16 08:15:50 ``` - `UNIX_TIMESTAMP([date])`：将日期时间值转换为对应的UNIX时间戳。示例： ```sql SELECT UNIX_TIMESTAMP('2024-03-16 08:18:50'); -- 1710595130 ``` ## 四、时间函数的实际应用案例 MySQL的时间函数在实际查询中有许多使用场景。我们一起看看常见的应用场景： ```mermaid %%{init: {'theme':'base', 'themeVariables': {'lineColor':'#ee712e'}}}%% graph LR A(时间函数的应用) B(查询当前时间) C(查询某一日期之前或之后的日期) D(比较日期或时间) E(格式化日期或时间的显示) F(计算日期或时间差异) G(根据当前时间进行条件查询) A ---> B A ---> C A ---> D A ---> E A ---> F A ---> G style A fill:#ee712e,stroke:#ee712e,stroke-width:2px,color:#FFFFFF style B fill:#FFFFFF,stroke:#ee712e,stroke-width:2px style C fill:#FFFFFF,stroke:#f6bca9,stroke-width:2px style D fill:#FFFFFF,stroke:#ee712e,stroke-width:2px style E fill:#FFFFFF,stroke:#f6bca9,stroke-width:2px style F fill:#FFFFFF,stroke:#ee712e,stroke-width:2px style G fill:#FFFFFF,stroke:#f6bca9,stroke-width:2px ``` > 注意：以上内容仅仅是MySQL时间函数的一小部分应用场景。实际开发中可以根据具体的需求和业务逻辑，将时间函数与其他查询条件、聚合函数等结合使用，以实现更复杂的查询和计算需求。 ## 五、注意事项 ### 5.1 时间区域设置对时间函数的影响 MySQL的时间区域设置对时间函数的影响是非常重要的，因为它确定了MySQL如何解释和处理日期、时间和时区相关的数据。以下是时间区域设置对时间函数的影响的几个方面： - **时区的默认值**：MySQL的时间函数会使用默认的时区设置来解释和处理日期、时间数据。如果未显式设置时区，则使用系统全局时区作为默认值。例如，`CURRENT_TIMESTAMP()` 函数将返回当前系统时区下的日期时间值。 - **时区的影响**：某些时间函数的结果可能会受到时区设置的影响。例如，`NOW()` 函数返回当前日期和时间，它受到系统时区的影响。如果时区设置为`Asia/Shanghai`，那么在芝加哥时区的服务器上调用 `NOW()` 函数将返回与在北京时区的服务器上调用 `NOW()` 函数不同的结果。 - **时区转换函数**：MySQL提供了一些函数用于在不同的时区之间进行转换，例如 `CONVERT_TZ()` 函数。这些函数依赖于时区设置和时区数据，以正确地执行时区转换操作。 - **时区偏移**：某些函数返回的结果可能包含时区偏移信息。例如，`TIMESTAMP` 类型的列存储的是不带时区的日期时间值，但在从数据库中检索出来时，MySQL会将其转换为当前时区的日期时间值，并根据需要添加时区偏移。 ## 六、总结时间函数在MySQL中具有重要性和实用性，它们提供了处理日期、时间和时区相关数据的强大功能；使得在数据库中进行时间操作和计算变得更加简便和灵活。 > 希望本文对您有所帮助。如果有任何错误或建议，请随时指正和提出。 > > 同时，如果您觉得这篇文章有价值，请考虑点赞和收藏。这将激励我进一步改进和创作更多有用的内容。 > > 感谢您的支持和理解！