在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
秒