將TIMESTAMP型別的差值轉化為秒的方法

兩個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要簡單得多。