CODEWITHSUNDEEP
shell
sumit vedi
sumit vedi

Reputation: 777

Getting the difference in minutes between two date columns

I have a file which contain two columns(start date time and end date time) I want to calculate the difference between these dates(in Mins). and store the difference in another column.

Source File

20121224 22:16:10|20121224 23:03:34

Output File

20121224 22:16:10|20121224 23:03:34|73

Can anyone help on this. Any help is appreciated.

Please tell me what is the problem in below command

echo "20121224 22:16:10|20121224 23:03:34"|awk -F'|' '{"date -d "$1" +%s"|getline d1;"date -d "$2" +%s"|getline d2;print $1"|"$2"|"d2-d1}'

I am getting below error while executing the command

sh: +%s: command not found

Please help me !!

Upvotes: 0

Views: 1486

Answers (3)

glenn jackman
glenn jackman

Reputation: 246744

awk -F '|' -v OFS='|' '
    function parse_date(d) {
        gsub(/:/, " ", d)
        return mktime(substr(d,1,4) " " substr(d,5,2) " " substr(d,7))
    }
    {
        diff = int((parse_date($1) - parse_date($2)) / 60)
        diff *= (diff < 0) ? -1 : 1   # awk is missing abs()
        print $0, diff
    }
' << END
20121224 22:16:10|20121224 23:03:34
END

20121224 22:16:10|20121224 23:03:34|47

Upvotes: 0

Vijay
Vijay

Reputation: 67211

#!/usr/bin/perl

use strict;
use warnings;

use DateTime                   qw( );
use DateTime::Format::Strptime qw( );

my $format = DateTime::Format::Strptime->new(
    pattern => '%Y%m%d %H:%M:%S',
);

open(FILE,"<your_file_name_with_path>");

while(<FILE>)
{
my @arr=split /|/,$_;
difference_mins $arr[0] $arr[1]

}

sub difference_mins
{
my ($istart,$istop)=($_[0],$_[1]);
my $start = $format->parse_datetime( $_[0] );
my $stop  = $format->parse_datetime( $_[1] );

$_->set_time_zone('local') for $start, $stop;
print "$istart | $istop | ";
print( $start->delta_ms($stop)->in_units('minutes'), "\n" );
}
  • NOTE: This is not tested though.

Upvotes: 0

Kent
Kent

Reputation: 195029

your example output is not correct.

20121224 23:03:34
20121224 22:16:10

tell me why the diff (in min) is 73? it should be anyway less than 60!

here is an awk oneliner working for your problem:

awk -F'|' -v q='"' '{"date -d"q $1 q" +%s"|getline d1;"date -d"q $2 q" +%s"|getline d2;print $0"|"(d2-d1)/60}' file

test with your example:

kent$  echo "20121224 22:16:10|20121224 23:03:34"|awk -F'|' -v q='"' '{"date -d"q $1 q" +%s"|getline d1;"date -d"q $2 q" +%s"|getline d2;print $0"|"(d2-d1)/60}'
20121224 22:16:10|20121224 23:03:34|47.4

Upvotes: 1

Related Questions