## Wednesday, November 13, 2013

### 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