CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
Busturdust
Busturdust

Reputation: 2495

MySql - Calculating distance in time using 2 values from 1 column (Poor design workaround)

I was granted access to a legacy database in order to do some statistics work.

I've so far gotten everything I need out of it, except I am trying to calculate a distance in time, using 5 values, stored in 4 columns (ARGGGHHH)

enter image description here

Above is a subsection of the database.

As you can see, I have start and stop date and time. I would like to calculate the distance in time from str_date + str_time to stp_date + stp_time

The issue I have is, the calculation should be performed differently depending on the second value in stp_time.

IFF second value = "DUR".... THen I can just take the first value "01:04:51" in this scenario

IFF second value = anything else. stp_time represents a timecode and not a duration. This must then calculate stp_time - str_time (accounting for date if not same date)

All data is 24 hour format. I have done work with conditional aggregation, but I have not figured this one out, and I have never worked with a malformed column like this before.

Any and all advice is welcome.

Thanks for reading

Upvotes: 0

Views: 59

Answers (2)

mkutyba
mkutyba

Reputation: 1427

SELECT
    CASE WHEN RIGHT(stp_time,3)="DUR"
        THEN
            TIMEDIFF(LEFT(stp_time,8), '00:00:00')
        ELSE
            TIMEDIFF(
                STR_TO_DATE(CONCAT(stp_date," ",LEFT(stp_time,8)), '%d/%b/%Y %H:%i:%s'),
                STR_TO_DATE(CONCAT(str_date," ",LEFT(str_time,8)), '%d/%b/%Y %H:%i:%s')
                )
    END AS diff
FROM so33289063

Upvotes: 2

Paul Stanley
Paul Stanley

Reputation: 4098

Try this out, you might want a where condition for the subquery With left and right:

SELECT IF(dur,stp,timediff(str,stp)) FROM(
SELECT STR_TO_DATE(CONCAT(str_date," ",LEFT(str_time,8)), 'd%/%b/%Y %H:%i:%s') as str,
       STR_TO_DATE(CONCAT(stp_date," ",LEFT(stp_time,8)), 'd%/%b/%Y %H:%i:%s') as stp,
       if(RIGHT(stp_time,3)="DUR",1,0) as dur
FROM my_table
) AS times

Upvotes: 1

Related Questions