PHP Mysql Calculating total flight hours per day from just two datetimes across day

Question

Row_id Sensor_ID  datetime_takeoff       datetime_landing
1      SFO        2013-09-18 04:34:22    2013-09-19 08:34:22
2      BWI        2013-09-18 04:34:22    2013-09-18 16:55:23 
3      BWI        2013-09-18 20:34:22    2013-09-19 10:34:22   
4      SFO        2013-09-19 15:21:22    2013-09-19 20:34:22   
5      BWI        2013-09-19 20:34:22    2013-09-20 06:15:16   
6      SFO        2013-09-19 23:47:22    2013-09-20 07:59:59   
7      BWI        2013-09-20 11:34:05    2013-09-21 02:05:21   
8      SFO        2013-09-20 10:28:56    NULL   
9      BWI        2013-09-21 04:09:57    NULL

This is for PHP and Mysql
These are airplane takeoff and landing datetime's for two planes SFO and BWI. I'm trying to calculate how many hours per day each plane is in the air. Flight duration is easy just DATETIMEDIFF but hours per day in the air is proving difficult.

I figure I can break it down into 2 steps:
1. For any take off and landing pair in the same day just get the datetimediff and if datetime_takeoff and datetime_landing are not in the same day then insert the total amount of hours from mindnight. I can run this as it occurs.

2. Run a check every day at midnight and check if the datetime_landing is NULL and IF the datetime_takeoff is TODAY, THEN calculate the hours to midnight. IF datetime_takeoff is not today then insert a value of 24hrs. I figure this will cover timedate_landings that are not on the same day/NULL

Does this logic make sense where all the data will be processed?

Just to be clear, what is your definition of total flight hours per day? Is it a fractional number of hours? Do you add up all the timespans and divide by the number of days they cover (partially)? — acfrancis, Oct 30 '13 at 23:03
It's the total number of flight hours that fall in any date and only that date, so if it crosses over to 12:01 or 00:01 that's another day and it's not included — TOO, Oct 31 '13 at 00:02

score 1 · Answer 1 · answered Oct 30 '13 at 23:22

Maybe something like this?

select
    sensor_ID,
    day,
    sum(flighttime) as sumflighttime
from
(
    (select
        sensor_ID,
        date_format(datetime_takeoff, '%Y-%m-%d') as day,
        case
            when date_format(datetime_takeoff, '%d') = date_format(datetime_landing, '%d')
                then subtime(datetime_landing, datetime_takeoff)
            else subtime(date_format(datetime_landing, '%Y-%m-%d'), datetime_takeoff)
        end flighttime
    from
        datatable
    ) b
    union all
    (select
        sensor_ID,
        date_format(datetime_landing, '%Y %m %d') as day,
        subtime(datetime_landing, date_format(datetime_landing, '%Y-%m-%d')) flighttime
    from
        datatable
    where
        date_format(datetime_takeoff, '%d') != date_format(datetime_landing, '%d')
    ) b
) t
group by
    sensor_ID,
    day

PHP Mysql Calculating total flight hours per day from just two datetimes across day

1 Answers1