How do I perform an arithmetic operation on Oracle timestamps (in SQL)?
I need to select an object that is valid in the middle of the reality of another object.
As a simplified example, I want to do something like this:
select
*
from
o1, o2
where
o1.from < (o2.to - ((o2.to - o2.from) /2) )
and
o1.to > (o2.to - ((o2.to - o2.from) /2) )
How can I calculate "(o2.to - ((o2.to - o2.from) / 2))" in SQL, assuming that in and out of timestamps?
0
danshala
a source
to share
1 answer
Have you tried what you wrote? It looks like it should work with me:
(o2.to - o2.from)
This will give you a partial difference per day, for example:
1 select (trunc(sysdate) - trunc(sysdate+1))/2 from dual
2*
SQL> /
(TRUNC(SYSDATE)-TRUNC(SYSDATE+1))/2
-----------------------------------
-.5
SQL>
Then if you add this result to the original date, you get the date and time at the midpoint:
SQL> select to_char(a.d, 'YYYY-MON-DD HH24:MI')
2 from
3 ( select trunc(sysdate) + (trunc(sysdate+1) - trunc(sysdate))/2 d from dual
4 ) a;
TO_CHAR(A.D,'YYYY
-----------------
2009-MAY-13 12:00
+2
a source to share