当前位置：首页 > article >正文

【Oracle】TIMESTAMP类型时间计算时间差

article 2024/9/21 20:53:03

在 Oracle 中，TIMESTAMP 和 TIMESTAMP(6) 的主要区别在于时间精度。TIMESTAMP 默认不包含微秒，而 TIMESTAMP(6) 支持微秒精度（6 位小数秒）。详细说明两者的区别、相互转换，并深入讨论如何计算两个 TIMESTAMP(6) 之间的时间差，包括微秒、毫秒、秒、分钟的计算。

1. `TIMESTAMP` 和 `TIMESTAMP(6)` 的区别

TIMESTAMP：表示日期和时间，没有微秒部分。默认格式是 YYYY-MM-DD HH24:MI:SS。
TIMESTAMP(6)：支持微秒的时间戳类型，可以保存到 6 位小数秒，格式为 YYYY-MM-DD HH24:MI:SS.FFFFFF，其中 FFFFF 表示小数秒部分。

2. 相互转换

Oracle 提供了灵活的转换方式来处理 TIMESTAMP 和 TIMESTAMP(6) 之间的转换。通过使用 CAST 函数，可以将它们进行互相转换。

将 TIMESTAMP(6) 转换为 TIMESTAMP（忽略微秒部分）：

SELECT CAST(timestamp6_col AS TIMESTAMP) FROM your_table;

将 TIMESTAMP 转换为 TIMESTAMP(6)（补充小数秒为 000000）：

SELECT CAST(timestamp_col AS TIMESTAMP(6)) FROM your_table;

3. 计算两个 `TIMESTAMP(6)` 类型的时间差

Oracle 中两个 TIMESTAMP(6) 类型的相减会返回一个 INTERVAL DAY TO SECOND 类型，表示时间差，包括天数、小时、分钟、秒以及小数秒。

假设有两个 TIMESTAMP(6) 的时间：

SELECT TO_TIMESTAMP('2024-09-13 10:30:15.123456', 'YYYY-MM-DD HH24:MI:SS.FF6') AS start_time,
       TO_TIMESTAMP('2024-09-13 12:45:35.654321', 'YYYY-MM-DD HH24:MI:SS.FF6') AS end_time
FROM dual;

要计算 start_time 和 end_time 的时间差，可以使用：

SELECT (end_time - start_time) AS time_diff
FROM (SELECT TO_TIMESTAMP('2024-09-13 10:30:15.123456', 'YYYY-MM-DD HH24:MI:SS.FF6') AS start_time,
             TO_TIMESTAMP('2024-09-13 12:45:35.654321', 'YYYY-MM-DD HH24:MI:SS.FF6') AS end_time
      FROM dual);

结果是一个 INTERVAL，类似于：

+00 02:15:20.530865

表示时间差为 2 小时 15 分 20 秒 530865 微秒。

4. 提取微秒、毫秒、秒、分钟数

提取微秒数

使用 EXTRACT 函数从 INTERVAL 中提取秒部分的微秒：

SELECT EXTRACT(SECOND FROM (end_time - start_time)) AS seconds_with_fraction,
       TO_NUMBER(TO_CHAR((end_time - start_time), 'FF6')) AS microseconds
FROM your_table;

EXTRACT(SECOND) 提取秒数和小数秒，而 TO_NUMBER(TO_CHAR(..., 'FF6')) 提取出微秒部分。

提取毫秒数

通过将微秒除以 1000 可以得到毫秒：

SELECT (TO_NUMBER(TO_CHAR((end_time - start_time), 'FF6')) / 1000) AS milliseconds
FROM your_table;

提取总秒数

将天、小时、分钟和秒数转换为总秒数：

SELECT EXTRACT(DAY FROM (end_time - start_time)) * 86400 +  -- 天数转秒
       EXTRACT(HOUR FROM (end_time - start_time)) * 3600 +  -- 小时转秒
       EXTRACT(MINUTE FROM (end_time - start_time)) * 60 +  -- 分钟转秒
       EXTRACT(SECOND FROM (end_time - start_time)) AS total_seconds
FROM your_table;

提取分钟数

将时间差转换为分钟（包括完整分钟和部分秒数）：

SELECT EXTRACT(DAY FROM (end_time - start_time)) * 1440 +  -- 天数转分钟
       EXTRACT(HOUR FROM (end_time - start_time)) * 60 +   -- 小时转分钟
       EXTRACT(MINUTE FROM (end_time - start_time)) AS total_minutes
FROM your_table;

假设有以下两个时间戳：

SELECT TO_TIMESTAMP('2024-09-13 10:30:15.123456', 'YYYY-MM-DD HH24:MI:SS.FF6') AS start_time,
       TO_TIMESTAMP('2024-09-13 12:45:35.654321', 'YYYY-MM-DD HH24:MI:SS.FF6') AS end_time
FROM dual;

要计算这两个时间戳的微秒、毫秒、秒、分钟差，可以使用以下查询：

SELECT EXTRACT(DAY FROM (end_time - start_time)) * 86400 +  -- 天数转秒
       EXTRACT(HOUR FROM (end_time - start_time)) * 3600 +  -- 小时转秒
       EXTRACT(MINUTE FROM (end_time - start_time)) * 60 +  -- 分钟转秒
       FLOOR(EXTRACT(SECOND FROM (end_time - start_time))) AS total_seconds,

       EXTRACT(DAY FROM (end_time - start_time)) * 1440 +   -- 天数转分钟
       EXTRACT(HOUR FROM (end_time - start_time)) * 60 +    -- 小时转分钟
       EXTRACT(MINUTE FROM (end_time - start_time)) AS total_minutes,

       TO_NUMBER(TO_CHAR((end_time - start_time), 'FF6')) AS microseconds,
       TO_NUMBER(TO_CHAR((end_time - start_time), 'FF6')) / 1000 AS milliseconds
FROM (SELECT TO_TIMESTAMP('2024-09-13 10:30:15.123456', 'YYYY-MM-DD HH24:MI:SS.FF6') AS start_time,
             TO_TIMESTAMP('2024-09-13 12:45:35.654321', 'YYYY-MM-DD HH24:MI:SS.FF6') AS end_time
      FROM dual);