The function INTERVAL DAY TO SECOND already exists from 9i, but I didn't use it that much before.
An Oracle By Example goes into more detail.
In APEX I created a quick example to show you how you can calculate the difference for dates. Behind the Calculate button I call a Process with this logic:
:P28_INTERVAL_DAY_TO_SECOND := CAST ( TO_DATE(:P28_end_date,'DD-MON-YYYY HH24:MI') AS TIMESTAMP WITH TIME ZONE )
- CAST ( TO_DATE(:P28_start_date,'DD-MON-YYYY HH24:MI') AS TIMESTAMP WITH TIME ZONE );
If you are looking for recurrent events (repeating intervals) you can use dbms_scheduler. In the early years of HTMLDB I created an APEX app called Agendimi which was basically an "online Outlook" for teachers. I wish I had the dbms_scheduler package to my disposal at that time!
Here's an example (found here, but adapted for APEX) to get the next 7 days (a plsql region in Apex on the same example page as above:
start_date := CAST ( TO_DATE(:P28_start_date,'DD-MON-YYYY HH24:MI') AS TIMESTAMP WITH TIME ZONE );
htp.p('Next 7 days (only weekdays)');
return_date_after := start_date;
FOR i IN 1..7
'FREQ=DAILY;BYHOUR=9;BYMINUTE=0;BYDAY=MON,TUE,WED,THU,FRI', start_date, return_date_after, next_run_date);
htp.p(TO_CHAR(next_run_date,'DD-MON-YYYY HH24:MI Day'));
return_date_after := next_run_date;
Another nice one FM... I needed a date format like 1.1.2009, so I thought d.m.yyyy, but I couldn't put that into Oracle. Try: select to_char(sysdate,'d.m.yyyy') from dual; You'll get a nice ORA-01821 date format not recognised. So how do you get that format then?
You need to put FM in front of your date format:
select to_char(sysdate,'FMdd.mm.yyyy') from dual;