Tuesday, August 11, 2015

Oracle and BASH : How to calculate how many minutes have elapsed between two timestamps

There are many ways to calculate minutes passed between two timestamps. The script below is to calculate how many minutes passed between two time stamps using oracle database.

Script : test.sh
====================
#!/bin/bash

# Create a function to accept two timestamps. The order of the timestapms (FROM_TIME, TO_TIME) does not affect the result.
fn_howmany_mins()
{
FROM_TIME=$1
TO_TIME=$2
MINS=`sqlplus -S "/ as sysdba " <<EOF
set echo off
set feedback off
set linesize 10;
set pagesize 0
set trimspool on
set space 0
set truncate on
select abs(round((to_date('$TO_TIME','YYYYMMDD_HH24MISS')-to_date('$FROM_TIME','YYYYMMDD_HH24MISS'))*1440)) from dual;
exit;
EOF`
MINS=`echo $MINS`
echo $MINS
}

#SET up test time stapms as below.
FROM_TIME=20150811_110601
TO_TIME=`date +"%Y%m%d_%H%M%S"`
echo "FROM_TIME=$FROM_TIME TO_TIME=$TO_TIME"

#Call the function to calculate the minutes.
fn_howmany_mins $FROM_TIME $TO_TIME

Sample output sh ./test.sh
==================
FROM_TIME=20150811_111458 TO_TIME=20150811_110601
9

In an actual usage remove the echo statement to get pure number . e.g. number 9 above