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

在MySQL中,经常需要在 DATETIMESTAMP 和字符串之间进行相互转换。以下是一些常见的转换方法:

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. 日期类型和时间戳类型之间的转换

  • DATETIMESTAMP:

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

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

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

    SELECT DATE(NOW()) AS timestamp_to_date;
    

4. UNIX_TIMESTAMPDATE/TIMESTAMP 的相互转换

  • UNIX_TIMESTAMPTIMESTAMP:

    使用 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;
    
  • TIMESTAMPUNIX_TIMESTAMP:

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

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

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

  • CAST 和 CONVERT 函数:

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

    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
### 如何在 MySQL 中将字符转换时间戳 在处理数据时,有时会遇到需要将字符串类型的日期时间信息转换成 `TIMESTAMP` 类型的情况。MySQL 提供了几种方式来实现这一目标。 #### 使用 STR_TO_DATE 函数 对于特定格式的字符串可以使用 `STR_TO_DATE()` 函数将其解析并转化为 `DATETIME` 或者 `TIMESTAMP` 类型的数据。此函数接受两个参数:一个是待转化的时间表示形式作为字符串;另一个是指定该字符串所遵循的具体格式模式串[^1]。 例如: ```sql SELECT STR_TO_DATE('2021-1-1 00:00:00', '%Y-%m-%d %H:%i:%s') AS converted_timestamp; ``` 这段 SQL 将返回一个具有正确格式化的 `TIMESTAMP` 值。 #### 利用 UNIX_TIMESTAMP FROM_UNIXTIME 组合 如果输入的是 Unix 时间戳(即自纪元以来经过了多少秒),可以直接利用这两个内置函数来进行相互之间的变换。不过这里讨论的是从常规字符表达转到标准时间戳,则只需关注后者即可[^2]。 比如下面的例子展示了如何把形如 `'YYYY-MM-DD HH:MM:SS'` 的字符串通过中间件变成真正的 `TIMESTAMP` 对象: ```sql SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2021-1-1 00:00:00')) AS unix_to_timestamp; ``` 需要注意的是,在实际应用过程中应当确保源字符串确实代表了一个合法有效的日期/时刻描述,并且其内部结构要严格匹配所提供的模板样式,否则可能会引发异常错误或者得到意想不到的结果。 另外值得注意的一点是在某些情况下可能还会碰到由于区域设置差异而导致的不同月份缩写的识别问题,因此建议尽可能采用完整的年月日小时分钟秒钟的形式书写日期时间字符串以减少不必要的麻烦[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值