#!perl
use strict;	
use warnings;
use DBI;

#create db
my $dbfile = 'database.sqlite';
unlink($dbfile);
my $dbh = DBI->connect('dbi:SQLite:dbname='.$dbfile , undef , undef , 
				{RaiseError =>1, AutoCommit =>0}) or die $DBI::errstr;
$dbh->do('CREATE TABLE DATA (F1 datetime,F2 INTEGER,F3,F4)'); 
$dbh->do('CREATE TABLE STARTED (F1 datetime,F2,F3)'); 
$dbh->commit;

# load data
my $sth = $dbh->prepare('INSERT INTO DATA VALUES (?,?,?,?)');
while (<DATA>){
  chomp;
  my @f = split ',';
  if (@f==4){
    $sth->execute(@f);
  } else {
    print "Input Line $. Skipped : $_\n";
  }
}
$dbh->commit; 

# select started records
$dbh->do("INSERT INTO STARTED
          SELECT F1,F2,F3 FROM DATA WHERE F4 LIKE '%started%'");
$dbh->commit; 

# calculate duration from start
my $ar = $dbh->selectall_arrayref("
   SELECT D.F1,D.F2,D.F3,D.F4,
   (1000*strftime('%s',D.F1))+D.F2 - (1000*strftime('%s',S.F1))-S.F2
   FROM DATA AS D
   LEFT JOIN STARTED AS S
            ON D.F3 = S.F3");       
for (@$ar){
  printf "@$_\n";
} 

$dbh->disconnect;

__DATA__