Find the difference between two dates in non-standard format, mysql

[oslon]

col1 col2

28/Apr/24 11:50 AM 29/Apr/24 5:29 PM

This is honestly an interesting sql project of mine(I can use node.js as well).

This is how I'd solve this manually:

29/Apr/24

Will be converted to

29/Apr/24

And it'll be converted to

29/04/24 ( There'll be a lookup table for Apr->04 mapping)

And the time will be converted 17:29 (no unit PM or AM)

Then,

I'll convert the col1 date to the similar format.

28/04/24

Then time to same format 24h format.

And subtract it.

But I can't make sql to do this as I've not much idea about SQL syntax. Can anyone help me? my alternative is using node.js

[requinix]

Step 1: stop storing them in a non-standard format and use DATETIMEs instead.

There is no step 2. Seriously. Use a standard format and this problem, and very likely other problems you've had or are yet to have, will go away.

[oslon]

I am not storing them, Jira is storing them in this format.

Export to Jira->CSV->Import to MySql.

[requinix]

...What are you using to view the CSV file?

edit: Nevermind, looks like it does use a localized format after all. Stupid Jira.

For PHP, use date_parse_from_format() or DateTime's createFromFormat, using the "d/M/y h:i a" format.
https://3v4l.org/NPZvF

Edited May 29 by requinix

[Barand]

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

 

[Barand]

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 |
+--------------+---------------------+---------------------+-----------+