[mysql] 좌표값 계산 후 도착시간 수정

-- 좌표값 계산 후 도착시간 수정
UPDATE flights f
JOIN airports dep ON f.departure_loc = dep.airport_id 
JOIN airports arr ON f.arrival_loc = arr.airport_id 
SET	
    f.arrival_time = 
        CONCAT(
            LPAD(
                HOUR(
                    DATE_ADD(
                        STR_TO_DATE(CONCAT(f.departure_time, '00'), '%H%i%s'), 
                        INTERVAL 
                        ROUND(
                            111.045 * DEGREES(
                                ACOS(
                                    COS(RADIANS(dep.latitude)) 
                                    * COS(RADIANS(arr.latitude)) 
                                    * COS(RADIANS(dep.longitude - arr.longitude)) 
                                    + SIN(RADIANS(dep.latitude)) 
                                    * SIN(RADIANS(arr.latitude))
                                )
                            ) / 500, 2
                        ) HOUR
                    )
                ) - 
                FLOOR(
                    (HOUR(
                        DATE_ADD(
                            STR_TO_DATE(CONCAT(f.departure_time, '00'), '%H%i%s'), 
                            INTERVAL 
                            ROUND(
                                111.045 * DEGREES(
                                    ACOS(
                                        COS(RADIANS(dep.latitude)) 
                                        * COS(RADIANS(arr.latitude)) 
                                        * COS(RADIANS(dep.longitude - arr.longitude)) 
                                        + SIN(RADIANS(dep.latitude)) 
                                        * SIN(RADIANS(arr.latitude))
                                    )
                                ) / 500, 2
                            ) HOUR
                        )
                    ) - 1) / 24
                ) * 24, 2, '0'
            ),
            LPAD(
                MINUTE(
                    DATE_ADD(
                        STR_TO_DATE(CONCAT(f.departure_time, '00'), '%H%i%s'), 
                        INTERVAL 
                        ROUND(
                            111.045 * DEGREES(
                                ACOS(
                                    COS(RADIANS(dep.latitude)) 
                                    * COS(RADIANS(arr.latitude)) 
                                    * COS(RADIANS(dep.longitude - arr.longitude)) 
                                    + SIN(RADIANS(dep.latitude)) 
                                    * SIN(RADIANS(arr.latitude))
                                )
                            ) / 500, 2
                        ) HOUR
                    )
                ), 2, '0'
            )
        )
WHERE 1=1
    and f.is_deleted = 0
--     AND f.flight_id = 80088;