Days between(integer value) two timestamps in oracle

Question

I want to find the day between two timestamps. The query must return an Integer value. I have a column value and a fixed date (like t.movementdate and '2014-07-23 00:00:00.0').

Adempiere already have a function with name "daysbetween". This function needs two parameters and type as Timestamp. you can send second parameter by using TO_DATE function. — Giri, Jul 24 '14 at 15:33

score 2 · Answer 1 · answered Jul 23 '14 at 09:00

You can try this

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);

score 0 · Answer 2 · answered Jul 23 '14 at 08:24

Good question. IMHO Oracle's timestamp arithmetic is quite ugly. If you need the difference in days, you can convert the TIMESTAMP to a DATE. Depending on your needs, you can also drop the minutes and hours with TRUNC. Once you have DATEs, you can simply substract them to get the difference in days:

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

Days between(integer value) two timestamps in oracle

2 Answers2