两个TIMESTAMP之差得到的是INTERVAL类型，而有时我们只需要得到两个时间相差的秒数，如果变成INTERVAL之后，想要获取这个值会非常麻烦。
比较常见的方法是使用EXTRACT来抽取获得的INTERVAL类型的日、时、分和秒来分别计算并求和：
SQL> CREATE TABLE t_timestamp (id NUMBER, t1 TIMESTAMP, t2 TIMESTAMP); TABLE created. SQL> INSERT INTO t_timestamp 2 VALUES (1, to_timestamp('20120603222324', 'yyyymmddhh24miss'), to_timestamp('20120526152354', 'yyyymmddhh24miss')); 1 ROW created. SQL> commit; Commit complete. SQL> SELECT t1 - t2 FROM t_timestamp WHERE id = 1; T1-T2 --------------------------------------------------------------------------- +000000008 06:59:30.000000 SQL> WITH t AS (SELECT t1 - t2 INTERVAL FROM t_timestamp WHERE id = 1) 2 SELECT EXTRACT(DAY FROM INTERVAL) * 86400 3 + EXTRACT(HOUR FROM INTERVAL) * 3600 4 + EXTRACT(MINUTE FROM INTERVAL) * 60 5 + EXTRACT(SECOND FROM INTERVAL) INTERVAL 6 FROM t; INTERVAL ---------- 716370 |
对于不需要考虑毫秒的情况而言，这种计算过于麻烦了，而对于DATE类型而言，计算差值非常方便，直接就可以返回两个日期相差的天数，在乘以86400就可以得到结果。
可惜的是，无论是ROUND还是TRUNC参数，都不支持TIMESTAMP类型：
SQL> SELECT trunc(t1, 'ss') FROM t_timestamp WHERE id = 1; SELECT trunc(t1, 'ss') FROM t_timestamp WHERE id = 1 * ERROR at line 1: ORA-01899: bad PRECISION specifier SQL> SELECT round(t1, 'ss') FROM t_timestamp WHERE id = 1; SELECT round(t1, 'ss') FROM t_timestamp WHERE id = 1 * ERROR at line 1: ORA-01899: bad PRECISION specifier |
其实对于这个问题，最简单的方法是利用隐式转换，变成DATE类型的操作：
SQL> SELECT (t1 - 0 - (t2 - 0)) * 86400 FROM t_timestamp; (T1-0-(T2-0))*86400 ------------------- 716370 |
当然最标准的方法还是显示的转换：
SQL> SELECT (CAST(t1 AS DATE) - CAST(t2 AS DATE)) * 86400 FROM t_timestamp; (CAST(T1ASDATE)-CAST(T2ASDATE))*86400 ------------------------------------- 716370 |
显然这种方便比利用EXTRACT要简单得多。