oracle中两个时间戳之间的天数(整数值)

Question

我想查找两个时间戳之间的日期。查询必须返回整型值。我有一个列值和一个固定的日期(如t.movementdate和'2014-07-23 00:00:00.0')。

Answer 1

你可以试试这个

CREATE TABLE t (movementdate TIMESTAMP);
INSERT INTO t VALUES (TIMESTAMP '1014-07-21 03:23:02.0');
INSERT INTO t VALUES (TIMESTAMP '2014-07-22 10:54:02.0');
select actual_diff,extract (day from actual_diff)+
                   extract (hour from actual_diff)/24            
                  +extract (minute from actual_diff)/(60*24) 
                  +extract (second from actual_diff)/(60*60*24)                                                              
                   diff_in_days 
from (select systimestamp- movementdate as actual_diff from t);

Answer 2

问得好。恕我直言，Oracle的时间戳算法相当丑陋。如果您需要以天为单位的差异，您可以将TIMESTAMP转换为DATE。根据您的需要，您还可以使用TRUNC降低分钟和小时。一旦你有了DATE s，你可以简单地减去它们来得到天的差值:

CREATE TABLE t (movementdate TIMESTAMP);
INSERT INTO t VALUES (TIMESTAMP '2014-07-21 03:23:02.0');
INSERT INTO t VALUES (TIMESTAMP '2014-07-22 10:54:02.0');
SELECT t.movementdate, DATE '2014-07-23' - trunc(t.movementdate) as daydiff  FROM t;
MOVEMENTDATE                  DAYDIFF
----------------------------  ----
21.07.2014 03:23:02,000000000 2
22.07.2014 10:54:02,000000000 1