Leaderboard

You can do it SQL when you load the CSV data into MySQL using the STR_TO_DATE() function to reformat the dates // CSV INPUT $input = 'Tom DiCanari,28/Apr/24 11:50 AM,29/Apr/24 5:29 PM'; // create tete table $pdo->exec("CREATE TABLE IF NOT EXISTS test1 (name varchar(50), timefrom datetime, timeuntil datetime)"); // process the input $stmt = $pdo->prepare("INSERT INTO test1 (name, timefrom, timeuntil) VALUES (?, STR_TO_DATE(?, '%d/%b/%y %h:%i %p'), STR_TO_DATE(?, '%d/%b/%y %h:%i %p'))"); $data = str_getcsv($input); $stmt->execute($data); then mysql> SELECT name -> , timefrom -> , timeuntil -> , timestampdiff(MINUTE, timefrom, timeuntil) as mins_diff -> FROM test1; +--------------+---------------------+---------------------+-----------+ | name | timefrom | timeuntil | mins_diff | +--------------+---------------------+---------------------+-----------+ | Tom DiCanari | 2024-04-28 11:50:00 | 2024-04-29 17:29:00 | 1779 | +--------------+---------------------+---------------------+-----------+

Some example code for you // INPUT DATES $col1 = '28/Apr/24 11:50 AM'; $col2 = '29/Apr/24 5:29 PM'; // FORMAT DEFINITIONS $jira = 'd/M/y h:i a'; $SQLformat = 'Y-m-d H:i:s'; $dt1 = DateTime::createFromFormat($jira, $col1); // create DateTime object $dt2 = DateTime::createFromFormat($jira, $col2); $d = $dt1->diff($dt2); // create DateInterval object echo $dt1->format($SQLformat) .'<br>'; // output dates echo $dt2->format($SQLformat) .'<br>'; printf("%d days %d hr %d min", $d->days, $d->h, $d->i); // show difference Output... 2024-04-28 11:50:00 2024-04-29 17:29:00 1 days 5 hr 39 min