【MySQL】在MySQL中STR_TO_DATE()以及其他用于日期和时间的转换

1.在MySQL中STR_TO_DATE()

在MySQL中，STR_TO_DATE() 函数用于将字符串转换为日期格式。这个函数非常有用，当你需要将文本数据转换为可由MySQL日期和时间函数处理的格式时。

1.1 语法

STR_TO_DATE() 函数的基本语法如下：

STR_TO_DATE(date_string, format_string)

• date_string：要转换的字符串。
• format_string：字符串中日期和时间的格式。

1.2 参数

• date_string：这是包含日期和/或时间的字符串。
• format_string：这是指定日期字符串中日期和时间格式的模板。它告诉MySQL如何解释字符串。

1.3 格式字符串

格式字符串定义了日期和时间数据的期望格式。它使用格式代码来指定日期的哪一部分应该出现在字符串的哪个位置。一些常用的格式代码包括：

• %Y：四位年份
• %y：两位年份
• %m：月份（01-12）
• %d：日（01-31）
• %H：小时（00-23）
• %i：分钟（00-59）
• %r：时间，格式为 hh:mm:ss AM 或 PM
• %T：时间，格式为 hh:mm:ss
• %M：分钟（00-59）
• %p：AM 或 PM

1.4 示例

1. 将字符串转换为日期：

   假设你有一个字符串 '2024-05-29'，你想将其转换为日期格式。

   SELECT STR_TO_DATE('2024-05-29', '%Y-%m-%d') AS converted_date;
   

   结果将是：

   converted_date: 2024-05-29
   

2. 处理包含时间的字符串：

   如果你有一个包含时间的字符串 '29-05-2024 15:23:45'，并且你想将其转换为日期时间格式。

   SELECT STR_TO_DATE('29-05-2024 15:23:45', '%d-%m-%Y %H:%i:%s') AS converted_datetime;
   

   结果将是：

   converted_datetime: 2024-05-29 15:23:45
   

3. 处理包含AM/PM的字符串：

   假设你有一个时间字符串 '07:23:45 PM'，你想将其转换为24小时制的格式。

   SELECT STR_TO_DATE('07:23:45 PM', '%h:%i:%s %p') AS converted_time;
   

   结果将是：

   converted_time: 19:23:45
   

4. 如果你有一个时间字符串 '14:22:01'，并且你想将其转换为时间格式，可以使用：

SELECT STR_TO_DATE('14:22:01', '%H:%i:%s') AS converted_time;

这将返回一个时间对象。

1.5注意事项

• 确保格式字符串与输入字符串完全匹配，否则 STR_TO_DATE() 将返回 NULL。
• 如果输入字符串或格式字符串不正确，函数可能不会按预期工作。
• 这个函数在处理不符合日期/时间格式的字符串时可能会返回错误或 NULL，因此在生产环境中使用时需要谨慎。

2. 其他用于日期和时间的转换

在MySQL中，除了 STR_TO_DATE() 函数，还有许多其他函数可以用于日期和时间的转换、提取和计算。以下是一些常用的日期和时间函数：

1. DATE(): 从日期时间表达式中提取日期部分。

   SELECT DATE('2024-05-29 15:23:45') AS date_only;
   

2. TIME(): 从日期时间表达式中提取时间部分。

   SELECT TIME('2024-05-29 15:23:45') AS time_only;
   

3. NOW(): 返回当前的日期和时间。

   SELECT NOW() AS current_datetime;
   

4. CURDATE(): 返回当前的日期。

   SELECT CURDATE() AS today;
   

5. CURTIME(): 返回当前的时间。

   SELECT CURTIME() AS current_time;
   

6. UNIX_TIMESTAMP(): 返回给定日期时间的UNIX时间戳。

   SELECT UNIX_TIMESTAMP('2024-05-29 15:23:45') AS unix_timestamp;
   

7. FROM_UNIXTIME(): 将UNIX时间戳转换为日期时间。

   SELECT FROM_UNIXTIME(1712868195) AS datetime_from_unix;
   

8. DATE_FORMAT(): 根据格式字符串格式化日期时间。

   SELECT DATE_FORMAT('2024-05-29 15:23:45', '%Y年%m月%d日 %H时%i分%s秒') AS formatted_datetime;
   

9. TIMESTAMP(): 将两个日期时间字符串合并为一个日期时间。

   SELECT TIMESTAMP('2024-05-29', '15:23:45') AS combined_datetime;
   

10. ADDDATE(): 给日期添加指定的时间间隔。

    SELECT ADDDATE('2024-05-29', INTERVAL 1 MONTH) AS new_date;
    

11. SUBDATE(): 从日期减去指定的时间间隔。

    SELECT SUBDATE('2024-05-29', INTERVAL 1 MONTH) AS new_date;
    

12. DATE_ADD(): 向日期添加指定的时间间隔。

    SELECT DATE_ADD('2024-05-29', INTERVAL 10 DAY) AS new_date;
    

13. DATE_SUB(): 从日期减去指定的时间间隔。

    SELECT DATE_SUB('2024-05-29', INTERVAL 10 DAY) AS new_date;
    

14. EXTRACT(): 从日期时间中提取指定的部分。

    SELECT EXTRACT(YEAR FROM '2024-05-29') AS year;
    

15. DAY(): 从日期中提取天数。

    SELECT DAY('2024-05-29') AS day_of_month;
    

16. MONTH(): 从日期中提取月份。

    SELECT MONTH('2024-05-29') AS month;
    

17. YEAR(): 从日期中提取年份。

    SELECT YEAR('2024-05-29') AS year;
    

18. HOUR(): 从时间或日期时间中提取小时数。

    SELECT HOUR('15:23:45') AS hour;
    

19. MINUTE(): 从时间或日期时间中提取分钟数。

    SELECT MINUTE('15:23:45') AS minute;
    

20. SECOND(): 从时间或日期时间中提取秒数。

    SELECT SECOND('15:23:45') AS second;
    

这些函数可以单独使用，也可以组合使用，以满足各种日期和时间处理的需求。