tbwshc

将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类型而言,tb计算差值非常方便,直接就可以返回两个日期相差的天数,在乘以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要简单得多。

 


posted on 2012-09-04 16:52 chen11-1 阅读(1861) 评论(0)  编辑  收藏