I have a table as follows:
Filename - varchar
Creation Date - Date format dd/mm/yyyy hh24:mi:ss
Oldest cdr date - Date format dd/mm/yyyy hh24:mi:ss
How can I calcuate the difference in hours minutes and seconds (and possibly days) between the two dates in Oracle SQL?
Thanks
You can substract dates in Oracle. This will give you the difference in days. Multiply by 24 to get hours, and so on.
SQL> select oldest - creation from my_table;
If your date is stored as character data, you have to convert it to a date type first.
SQL> select 24 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi')
- to_date('2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')) diff_hours
from dual;
DIFF_HOURS
----------
2.5
Note:
This answer applies to dates represented by the Oracle data type DATE.
Oracle also has a data type TIMESTAMP, which can also represent a date (with time). If you subtract TIMESTAMP values, you get an INTERVAL; to extract numeric values, use the EXTRACT function.
DATE, you get the number of days (as a NUMBER). However, if you subtract TIMESTAMP values, you get INTERVALDS. Probably you are working with TIMESTAMP and not DATE values. I edited the answer.
DATE and TIMESTAMP data types have a time (hours, minutes and seconds) component. TIMESTAMP also has a fractional seconds time component (and potentially time zone components).
To get result in seconds:
select (END_DT - START_DT)*60*60*24 from MY_TABLE;
Check [https://community.oracle.com/thread/2145099?tstart=0][1]
select
extract( day from diff ) Days,
extract( hour from diff ) Hours,
extract( minute from diff ) Minutes
from (
select (CAST(creationdate as timestamp) - CAST(oldcreationdate as timestamp)) diff
from [TableName]
);
This will give you three columns as Days, Hours and Minutes.
declare
strTime1 varchar2(50) := '02/08/2013 01:09:42 PM';
strTime2 varchar2(50) := '02/08/2013 11:09:00 PM';
v_date1 date := to_date(strTime1,'DD/MM/YYYY HH:MI:SS PM');
v_date2 date := to_date(strTime2,'DD/MM/YYYY HH:MI:SS PM');
difrence_In_Hours number;
difrence_In_minutes number;
difrence_In_seconds number;
begin
difrence_In_Hours := (v_date2 - v_date1) * 24;
difrence_In_minutes := difrence_In_Hours * 60;
difrence_In_seconds := difrence_In_minutes * 60;
dbms_output.put_line(strTime1);
dbms_output.put_line(strTime2);
dbms_output.put_line('*******');
dbms_output.put_line('difrence_In_Hours : ' || difrence_In_Hours);
dbms_output.put_line('difrence_In_minutes: ' || difrence_In_minutes);
dbms_output.put_line('difrence_In_seconds: ' || difrence_In_seconds);
end ;
Hope this helps.
You may also try this:
select to_char(to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')+(end_date - start_date),'hh24:mi:ss')
as run_time from some_table;
It displays time in more human readable form, like: 00:01:34. If you need also days you may simply add DD to last formatting string.
Calculate age from HIREDATE to system date of your computer
SELECT HIREDATE||' '||SYSDATE||' ' ||
TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) ||' YEARS '||
TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE))-(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)*12))||
'MONTHS' AS "AGE " FROM EMP;
You could use to_timestamp function to convert the dates to timestamps and perform a substract operation.
Something like:
SELECT
TO_TIMESTAMP ('13.10.1990 00:00:00','DD.MM.YYYY HH24:MI:SS') -
TO_TIMESTAMP ('01.01.1990:00:10:00','DD.MM.YYYY:HH24:MI:SS')
FROM DUAL
In oracle 11g
SELECT end_date - start_date AS day_diff FROM tablexxx
suppose the starT_date end_date is define in the tablexxx
select days||' '|| time from (
SELECT to_number( to_char(to_date('1','J') +
(CLOSED_DATE - CREATED_DATE), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(CLOSED_DATE - CREATED_DATE), 'HH24:MI:SS') time
FROM request where REQUEST_ID=158761088 );
If you want something that looks a bit simpler, try this for finding events in a table which occurred in the past 1 minute:
With this entry you can fiddle with the decimal values till you get the minute value that you want. The value .0007 happens to be 1 minute as far as the sysdate significant digits are concerned. You can use multiples of that to get any other value that you want:
select (sysdate - (sysdate - .0007)) * 1440 from dual;
Result is 1 (minute)
Then it is a simple matter to check for
select * from my_table where (sysdate - transdate) < .00071;
If you select two dates from 'your_table' and want too see the result as a single column output (eg. 'days - hh:mm:ss') you could use something like this. First you could calculate the interval between these two dates and after that export all the data you need from that interval:
select extract (day from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date,created_date))),
'day'))
|| ' days - '
|| extract (hour from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date,created_date))),
'day'))
|| ':'
|| extract (minute from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date, created_date))),
'day'))
|| ':'
|| extract (second from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date, created_date))),
'day'))
from your_table
And that should give you result like this: 0 days - 1:14:55
(TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*60*60*24 sum_seconds,
(TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*60*24 sum_minutes,
(TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*24 sum_hours,
(TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi')) sum_days
select to_char(actual_start_date,'DD-MON-YYYY hh24:mi:ss') start_time,
to_char(actual_completion_date,'DD-MON-YYYY hh24:mi:ss') end_time,
floor((actual_completion_date-actual_start_date)*24*60)||'.'||round(mod((actual_completion_date-actual_start_date)*24*60*60,60)) diff_time
from fnd_concurrent_requests
order by request_id desc;
If You want get date defer from using table and column.
SELECT TO_DATE( TO_CHAR(COLUMN_NAME_1, 'YYYY-MM-DD'), 'YYYY-MM-DD') -
TO_DATE(TO_CHAR(COLUMN_NAME_2, 'YYYY-MM-DD') , 'YYYY-MM-DD') AS DATEDIFF
FROM TABLE_NAME;
This will count time between to dates:
SELECT
(TO_CHAR( TRUNC (ROUND(((sysdate+1) - sysdate)*24,2))*60,'999999')
+
TO_CHAR(((((sysdate+1)-sysdate)*24)- TRUNC(ROUND(((sysdate+1) - sysdate)*24,2)))/100*60 *100, '09'))/60
FROM dual
Here's another option:
with tbl_demo AS
(SELECT TO_DATE('11/26/2013 13:18:50', 'MM/DD/YYYY HH24:MI:SS') dt1
, TO_DATE('11/28/2013 21:59:12', 'MM/DD/YYYY HH24:MI:SS') dt2
FROM dual)
SELECT dt1
, dt2
, round(dt2 - dt1,2) diff_days
, round(dt2 - dt1,2)*24 diff_hrs
, numtodsinterval((dt2 - dt1),'day') diff_dd_hh_mm_ss
from tbl_demo;
Single query that will return time difference of two timestamp columns:
select INS_TS, MAIL_SENT_TS, extract( hour from (INS_TS - MAIL_SENT_TS) ) timeDiff
from MAIL_NOTIFICATIONS;
select round( (tbl.Todate - tbl.fromDate) * 24 * 60 * 60 ) from table tbl
for oracle sql I justbn did this and works perfect :
SELECT trunc(date_col_1) - trunc(date_col_2)
FROM TABLE;
SQL> select
2 numtodsinterval(
3 to_date('2024-05-04 03:02:01', 'yyyy-mm-dd hh24:mi:ss') - to_date('2024-05-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
4 , 'DAY') as diff_interval
5 from dual
6 ;
DIFF_INTERVAL
---------------------------------------
+000000001 03:02:01.000000000
SQL>
select END_TIME, START_TIME,to_char(END_TIME,'HH24') end_time, to_char(START_TIME,'HH24') start_time, case when (to_number(to_char(START_TIME,'HH24')) > 12) then extract( HOUR from ( cast(END_TIME+ INTERVAL '1' DAY AS TIMESTAMP) - cast( START_TIME AS TIMESTAMP) ) ) else extract( HOUR from ( cast(END_TIME AS TIMESTAMP) - cast(START_TIME AS TIMESTAMP) ) ) end working_hours from DB_TABLE
$sql="select bsp_bp,user_name,status,
to_char(ins_date,'dd/mm/yyyy hh12:mi:ss AM'),
to_char(pickup_date,'dd/mm/yyyy hh12:mi:ss AM'),
trunc((pickup_date-ins_date)*24*60*60,2),message,status_message
from valid_bsp_req where id >= '$id'";
