how i calculate different time in milisecond 2 column in millisecond using bash?

Question

I have a text file with content as below :

2017-11-24 00:00:03.775, 2017-11-24 00:00:03.821
2017-11-24 00:00:03.775, 2017-11-24 00:00:03.827
2017-11-24 00:00:03.775, 2017-11-24 00:00:03.894
2017-11-24 00:00:08.775, 2017-11-24 00:00:08.848
2017-11-24 00:00:08.776, 2017-11-24 00:00:08.828
2017-11-24 00:00:08.776, 2017-11-24 00:00:08.858

I want to calculate avg different time 2 column split by ',' and max different time in millisecond time. Please help on this..

Answer 1

Perl to the rescue!

perl -MTime::Piece -lne'
    @ms = ();
    push @ms, sprintf "%-3s", $1 while s/\.([0-9]+)//;
    s/ /0/g for @ms;
    @dates = map Time::Piece->strptime($_, "%Y-%m-%d %H:%M:%S"),
             split /, /;
    print 1000 * ($dates[1] - $dates[0]) + $ms[1] - $ms[0];
' -- input.txt

• -l removes newlines from input and adds them to output.
• -n reads the input line by line.
• -M loads a library, in this case Time::Piece. As it can't handle milliseconds, we need to extract them from the timestamps and store them in the @ms array.

Tested with a slightly more challenging input:

2017-11-24 02:03:04.775, 2017-11-24 02:03:04.821
2017-11-24 23:59:58.902, 2017-11-25 00:00:02.821
2017-10-30 23:59:58.001, 2017-11-01 00:00:02.821
2017-10-29 23:59:58.001, 2017-11-01 00:00:02.821
2017-12-31 23:59:58.902, 2018-01-01 00:00:02.821
2017-11-23 00:00:01.225, 2017-11-23 00:00:01.33

As timezone isn't specified, the script can produce wrong output if the timestamps happen to have a different Daylight Saving Time setting.

Update: Code added to handle milliseconds < 100.

Update2: Output format fixed.

#!/bin/bash
perl -MTime::Piece -lne'
    @ms = ();
    push @ms, sprintf "%-3s", $1 while s/\.([0-9]+)//;
    s/ /0/g for @ms;
    @dates = map Time::Piece->strptime($_, "%Y-%m-%d %H:%M:%S"),
             split /, /;
    warn "End date different to start date!\n"
        if $dates[0]->ymd ne $dates[1]->ymd;
    print join " ", $dates[0]->ymd,
                    sprintf("%02d", $dates[0]->hour),
                    1000 * ($dates[1] - $dates[0]) + $ms[1] - $ms[0];
' -- input.txt

Answer 2

Check it out!

#!/bin/bash

split -l 1 -d -a1 tuyen.txt txt
totalline=$(wc -l < tuyen.txt)
#echo $totalline

for (( i=0; i<=$totalline; i++ ))
do
    cat txt$i | awk -F ', ' '{print $1}' > fulldaytime1txt$i
    cat txt$i | awk -F ', ' '{print $2}' > fulldaytime2txt$i
    rm -rf txt$i

    cat fulldaytime1txt$i | awk -F ' ' '{print $1}' > fullday1txt$i
    cat fulldaytime2txt$i | awk -F ' ' '{print $1}' > fullday2txt$i
    cat fulldaytime1txt$i | awk -F ' ' '{print $2}' > fulltime1txt$i
    cat fulldaytime2txt$i | awk -F ' ' '{print $2}' > fulltime2txt$i


    #split milisecond of each time
    read -r mls1$i <<<$(echo $(cat fulltime1txt$i | awk -F'.' '{print $2}'))
    read -r mls2$i <<<$(echo $(cat fulltime2txt$i | awk -F'.' '{print $2}'))
    #echo $((mls1$i)) $((mls2$i))

    #calculate milisec difference of each line
    read -r emls$i <<<$(expr $((mls2$i)) - $((mls1$i)))
    #echo $((emls$i))

    #calculate datediff of each line
    read -r days1txt$i <<<$(date -u -d $(cat fullday1txt$i) +%j)
    read -r days2txt$i <<<$(date -u -d $(cat fullday2txt$i) +%j)
    read -r datedifftxt$i <<<$(expr $((days2txt$i)) - $((days1txt$i)))

    #calculate secondsdiff of each line
    read -r sec1txt$i <<<$(date -u -d $(cat fulltime1txt$i) +%s)
    read -r sec2txt$i <<<$(date -u -d $(cat fulltime2txt$i) +%s)
    read -r secondsdiff$i <<<$(expr $((sec1txt$i)) - $((sec2txt$i)))

    rm -rf fulltime1txt$i fulltime2txt$i fullday1txt$i fullday2txt$i fulldaytime1txt$i fulldaytime2txt$i


    #Mac dinh thoi gian 2 luon lon hon thoi gian 1

    if [ ! $((emls$i)) -lt 0 ]
    then
        read -r htxt$i <<<$(echo $((secondsdiff$i))/3600 | bc)
        read -r mtxt$i <<<$(echo $((secondsdiff$i))/60 - 60*$((htxt$i)) | bc)
        read -r stxt$i <<<$(echo $((secondsdiff$i))%60 | bc)

        echo "Time Diff $(expr $i + 1): $((datedifftxt$i)) days,$((htxt$i)):$((mtxt$i)):$((stxt$i)).$((emls$i))" >> results.txt
    elif [[ ! $((secondsdiff$i)) -lt 1 ]] 
        then
            read -r secondsdiff$i <<<$(expr $((secondsdiff$i)) - 1)

            read -r htxt$i <<<$(echo $((secondsdiff$i))/3600 | bc)
            read -r mtxt$i <<<$(echo $((secondsdiff$i))/60 - 60*$((htxt$i)) | bc)
            read -r stxt$i <<<$(echo $((secondsdiff$i))%60 | bc)

            echo "Time Diff $(expr $i + 1): $((datedifftxt$i)) days,$((htxt$i)):$((mtxt$i)):$((stxt$i)).$(expr $((emls$i)) + 1000)" >> results.txt
        elif [[ ! $((datedifftxt$i)) -lt 1 ]]
            then
                read -r datedifftxt$i <<<$(expr $((datedifftxt$i)) - 1)
                read -r secondsdiff$i <<<$(expr $((secondsdiff$i)) + 86399)

                read -r htxt$i <<<$(echo $((secondsdiff$i))/3600 | bc)
                read -r mtxt$i <<<$(echo $((secondsdiff$i))/60 - 60*$((htxt$i)) | bc)
                read -r stxt$i <<<$(echo $((secondsdiff$i))%60 | bc)

                echo "Time Diff $(expr $i + 1): $((datedifftxt$i)) days,$((htxt$i)):$((mtxt$i)):$(expr $((stxt$i)) - 1).$(expr $((emls$i)) + 1000)" >> results.txt
            fi
        fi
    fi
done