Calculate date/time difference form entries in two files in bash script

Question

I have two files (STARTED and COMPLETED) that look like this:

STARTED FILE:

2018-01-30 10:21:41
2018-01-17 12:22:50
2018-06-27 23:09:20
INVALID
INVALID
... for 800 Rows

COMPLETED FILE:

2018-01-30 10:23:54
2018-01-17 13:23:45
2018-06-28 06:10:56
INVALID
INVALID
... for 800 rows

I need to create a third file that has the result of difference of each row for file2 and file1 - to get the time lapse.

NEW 3rd FILE:

00:02:13
01:00:55
07:01:36
INVALID     //Where any instance of invalid in either file remain in the new file.
INVALID

... for 800 rows

I was able to get this to work manual using this command but no luck looping through my file:

string1="10:21:41"
string2="10:23:54"
StartDate=$(date -u -d "$string1" +"%s")
FinalDate=$(date -u -d "$string2" +"%s")
date -u -d "0 $FinalDate sec - $StartDate sec" +"%H:%M:%S"

> 00:02:13

THANKS SO MUCH SouravGosh! This worked PERFECTLY using base system utilities! — SSDdude, Dec 21 '18 at 13:18

ralz · Accepted Answer · 2018-12-19T16:24:12.100

as oneliner

while read -r StartDate  && read -r FinalDate <&3; do if [[ ${StartDate} != "INVALID" && ${FinalDate} != "INVALID" ]]; then diff=$(expr $(date -d "${FinalDate}" +"%s") - $(date -d "${StartDate}" +"%s")); printf '%dd:%dh:%dm:%ds\n' $((${diff}/86400)) $((${diff}%86400/3600)) $((${diff}%3600/60)) $((${diff}%60));else echo INVALID; fi; done < startedfile 3<finishedfile

as a script

#!/bin/bash

while read -r StartDate  && read -r FinalDate <&3; do 
    if [[ ${StartDate} != "INVALID" && ${FinalDate} != "INVALID" ]]; then 
        diff=$(expr $(date -d "${FinalDate}" +"%s") - $(date -d "${StartDate}" +"%s")); 
        printf '%dd:%dh:%dm:%ds\n' $((${diff}/86400)) $((${diff}%86400/3600)) $((${diff}%3600/60)) $((${diff}%60));
    else 
        echo INVALID; 
    fi; 
done < startedfile 3<finishedfile

It will give output like this:

0d:0h:2m:13s
0d:1h:0m:55s
INVALID
0d:7h:1m:36s
INVALID
INVALID

you can then just output that to file you want.

EDIT

As suggested in the comments, this can be simplified by installing dateutils package and using datediff command.

while read -r StartDate  && read -r FinalDate <&3; do if [[ ${StartDate} != "INVALID" && ${FinalDate} != "INVALID" ]]; then datediff "${StartDate}" "${FinalDate}" -f "%dd:%Hh:%Mm:%Ss";else echo INVALID; fi; done < started.txt 3<finished.txt

In a script

#!/bin/bash

while read -r StartDate  && read -r FinalDate <&3; do 
    if [[ ${StartDate} != "INVALID" && ${FinalDate} != "INVALID" ]]; then 
        datediff "${StartDate}" "${FinalDate}" -f "%dd:%Hh:%Mm:%Ss";
    else 
        echo INVALID; 
    fi; 
done < startedfile 3<finishedfile

As simplification, I'd suggest using ddiff from the dateutils package which allows subtracting date strings directly: dateutils.ddiff "$startdate" "$enddate" gives the difference in seconds or a format defined by e.g. -f %H:%M:%S, i.e. matching date's formatting system. — FelixJN, Dec 19 '18 at 16:04

jesse_b · Answer 2 · 2018-12-19T16:12:02.310

I believe I was able to solve your problem with the following bash script:

#!/usr/bin/env bash

sfile=/path/to/start
efile=/path/to/end
ofile=/path/to/out
n=0

while read -r line; do
    ((n++))
    if [[ $line == 'INVALID' ]]; then
        echo "INVALID"
        continue
    fi
    start=$(date -u -d "$line" "+%s")
    end=$(date -u -d "$(sed -n "${n}p" "$efile")" "+%s")
    date -u -d "0 $end sec - $start sec" +"%H:%M:%S"
done<"$sfile" >"$ofile"

This will read each line of the start file and compare it to the matching line in the end file. If the line contains "INVALID" it will echo "INVALID" and skip to the next iteration of the loop.

With GNU date you can get it to read dates from a file with -f. This means that you could potentially get away with just one or two invocations of date instead of 800. — Kusalananda, Dec 19 '18 at 17:38

Kusalananda · Answer 3 · 2018-12-19T16:27:09.213

Using ddiff from GNU dateutils, and bash:

#!/bin/bash

paste STARTED COMPLETED |
while IFS=$'\t' read start compl; do
    if [ "$start" = "INVALID" ] || [ "$compl" = "INVALID" ]; then
        echo 'INVALID'
    else
        ddiff -f '%0H:%0M:%0S' "$start" "$compl"
    fi
done

Assuming the input files are called STARTED and COMPLETED, this creates a tab-delimited input to the while loop with the start times in the first field and the completion times in the second field. It reads these and checks whether any of the two times are INVALID. If not, it calls ddiff with them.

The output of this could be saved to a file with a redirection after done at the end, or after the script name on the command line when invoking the script.

Running it on the supplied data:

$ bash script.sh
00:02:13
01:00:55
07:01:36
INVALID
INVALID

Stéphane Chazelas · Answer 4 · 2018-12-19T17:27:12.967

With zsh:

#! /bin/zsh -
# usage: that-script file1 file2
zmodload zsh/datetime
while
  IFS= read -ru3 a &&
   IFS= read -ru4 b
do
  if
    strftime -rs at '%Y-%m-%d %H:%M:%S' "$a" 2> /dev/null &&
      strftime -rs bt '%Y-%m-%d %H:%M:%S' "$b" 2> /dev/null
  then
    d=$((bt - at))
    printf '%02d:%02d:%02d\n' $((d/3600)) $(((d/60)%60)) $((d%60))
  else
     printf '%s\n' $a
  fi
done 3< ${1?} 4< ${2?}

That's still using a shell loop to process text which is generally considered bad practice but at least here we're only using builtin commands which means performance is not going to be as dreadful as if you were invoking two external GNU date commands for each line of input.

Calculate date/time difference form entries in two files in bash script

4 Answers4