MySQL中日期和时间戳的转换：字符到DATE和TIMESTAMP的相互转换

在MySQL中，经常需要在DATE、TIMESTAMP和字符串之间进行相互转换。以下是一些常见的转换方法：

1.字符串到日期/时间类型

• 字符串转DATE:

  使用STR_TO_DATE()函数将字符串转换为DATE类型。你需要提供字符串的格式。

  SELECT STR_TO_DATE('2024-08-24', '%Y-%m-%d') AS my_date;

• 字符串转TIMESTAMP:

  同样使用STR_TO_DATE()函数，但格式要包括时间部分。

  SELECT STR_TO_DATE('2024-08-24 14:35:00', '%Y-%m-%d %H:%i:%s') AS my_timestamp;

2.日期/时间类型到字符串

• DATE转字符串:

  使用DATE_FORMAT()函数将DATE转换为指定格式的字符串。

  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS date_str;

• TIMESTAMP转字符串:

  使用DATE_FORMAT()函数将TIMESTAMP转换为指定格式的字符串。

  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS timestamp_str;

3.日期类型和时间戳类型之间的转换

• DATE转TIMESTAMP:

  DATE类型只有日期部分，没有时间部分，MySQL 在转换时会默认将时间部分设置为00:00:00。

  SELECT CAST('2024-08-24' AS DATETIME) AS date_to_timestamp;

• TIMESTAMP转DATE:

  使用DATE()函数从TIMESTAMP中提取日期部分。

  SELECT DATE(NOW()) AS timestamp_to_date;

4.UNIX_TIMESTAMP和DATE/TIMESTAMP的相互转换

• UNIX_TIMESTAMP转TIMESTAMP:

  使用FROM_UNIXTIME()函数将UNIX_TIMESTAMP转换为TIMESTAMP。

  -- 将UNIX时间戳转为时间戳 SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()) AS unix_to_timestamp; -- UNIX时间戳转时间戳 (如果是13位需要除1000) SELECT FROM_UNIXTIME(1692874200) AS unix_to_timestamp;

• TIMESTAMP转UNIX_TIMESTAMP:

• 使用UNIX_TIMESTAMP()函数将TIMESTAMP转换为UNIX_TIMESTAMP。

  -- 将时间戳转换为UNIX时间戳 SELECT UNIX_TIMESTAMP(NOW()) AS timestamp_to_unix;

5.直接通过类型转换函数

• CAST 和 CONVERT 函数:

  使用CAST()或CONVERT()函数可以在DATE、TIMESTAMP和字符串之间进行转换。

  SELECT CAST('2024-08-24 14:35:00' AS DATE) AS cast_to_date; SELECT CONVERT(NOW(), CHAR) AS convert_to_string;

6. 字符串到日期或时间戳，带时区的转换

• CONVERT_TZ: 将时间戳从一个时区转换到另一个时区。

  – 将UTC时间戳转换为东八区时间
  SELECT CONVERT_TZ(‘2024-08-24 06:00:00’, ‘+00:00’, ‘+08:00’);

7. 字符串直接转换为时间戳

• 如果字符串格式与时间戳的默认格式一致，可以直接进行转换，这会自动将字符串转换为时间戳。注意这种方法仅适用于字符串格式精确匹配默认的DATETIME格式。

  SELECT ‘2024-08-24 14:30:00’ + 0 AS timestamp_value;

常用的格式化符号：

• %Y年（四位）
• %m月（两位）
• %d日（两位）
• %H小时（24小时制）
• %i分钟
• %s秒