Caculate Time Elapsed Between Two Dates

Problem

We want to find out how many seconds (or hours or days) elapsed between two time stamps. In building predictive models, variables like time since last credit card transaction, length of employment, etc., could be very useful in predicting fraudulent credit card transaction or claims. In the following example, we have a transaction begin and end time in our table. We want to find out how many minutes it takes to process a transaction.
SQL> select txn_id, to_char(txn_begin, 'YYYYMMDDHH24:MI:SS') txn_begin, to_char(txn_end, 'YYYYMMDDHH24:MI:SS') text_end from tbl_txn_small where ro wnum <=1;

TXN_ID	TXN_BEGIN	TEXT_END
1001	2013110509:21:03	2013110509:27:01

Solution

The days elapsed between two time stamps can be easily done by subtracting the end time from the beginning time. To convert the elapsed days in minutes or seconds we simple multiple it by 24X60 or 24X60X60.

SQL> select txn_id, to_char(txn_begin, 'YYYYMMDDHH24:MI:SS') txn_begin, to_char(txn_end, 'YYYYMMDDHH24:MI:SS') text_end, (txn_end-txn_begin)*24 hou rs_elpased, (txn_end-txn_begin)*24*60 minutes_elapsed, (txn_end-txn_begin)*24*60*60 sec_elapsed from tbl_txn_small where rownum <=1;

TXN_ID	TXN_BEGIN	TEXT_END	HOURS_ELPASED	MINUTES_ELAPSED	SEC_ELAPSED
1001	2013110509:21:03	2013110509:27:01	.099444444	5.96666667	358