Thanks, I'll Work the code up in a demo to see if it matches what I now have.
NP, and please take your time to see how I did things. In case of question then feel free to ask.
But I can't trust the results. This is on the flight from KMEK to KPHX.
Yours or mine ?
I have no idea why you wouldn't be able to trust the results (whether they be mine or yours, unless we are still making an error somewhere)
I'm departing at 15:23:30 and arriving at 16:44:03. It's about an hour and 16 difference.
An hour and 16 difference
between two different timezones. Look at my time-table for that flight, specifically at the UTC times. That is the real duration of the flight (well, theoretically).
But then there is 2 hours difference between KMEK and KPHX. So then I'm wondering.
Again take a closer look at my timetable if you will.
Location ICAO UTC Local UTC+ DST+
Departure KMKE 2020-06-19 20:49:09 2020-06-19 15:49:09 -360 60
Flight B787 2020-06-19 23:44:03 - 0 0
Arrival KPHX 2020-06-19 23:44:08 2020-06-19 16:44:08 -420 0
UTC offset for KMKE = -360 min (= -6 hours)
UTC offset for KPHX = -420 min (= -7 hours)
But.... KMKE also has daylight saving active. so:
UTC offset + DST offset for KMKE = -360 + 60 = -300 mins (= -5 hours)
UTC offset + DST offset for KPHX = -420 + 0 = -420 mins (= -7 hours)
Which accounts for the 2 hour (virtual) time-difference between these two different timezones.
So, when UTC is 17:00 then that means it is 12 o'clock (local time) at/in KMKE and 10 o'clock (local time) at/in KPHX.
Would you fly an hour from 12:00 to 13:00 (based on timezone PHX) then the time in/at KPHX would be 11:00.
I have no idea what I could say in addition to that, in order to make things easier to understand. That is how time and timezones work.
That is why I calculate everything absolute to UTC, and only in the end convert those values back to local timezone (including DST). All times in the timetable which you can find at the column UTC actually makes sense in relation to the flight and other data. The local times however don't make any sense and can make your head explode
(timezones/offsets are but a mere virtual/imaginary construct, and imho serves no purpose whatsoever other than screw with my internal clock for instance each and every time DST is (de-)activated).
My aim with the table was that you would be able to see everything with a single overview of all mentioned items. You should focus there on the UTC times, as they are the real true absolute date-time values.
Other than that, I had a quick view at the meaning of the words departure and arrival as used in aviation.
If you take a closer look at PHX arrival time, in relation to the Flight Time then you can see it is only a small distance/time-diff between them (1.2 km, about 5 seconds based on cruise speed). However, even though i have no idea what ADS records includes exactly, it would imho not be strange to assume that the last 5 seconds is not being flown at cruise-speed, rather it is the taxi from the run-way to the terminal. That would explain why it wouldn't be 'fair' to still add the 'waste'-time of descending, taxing etc to that arrival time (the plane is already busy 'performing' that 'waste' sequence). On the other hand you would have to account for that at the departure location, and would explain the difference between the/my theoretical calculation and actual flight information.
For the second test (Kansai - Amsterdam) you can see that, because of the ADS record that was taken mid-flight (and not at beginning/end), when you add additional 'waste'-time for departure and arrival the/my calculated times seems to make more or less sense in relation to the actual flight data.
That's the only time I get is a Lat/Lon and a Unix/GMT but I suspect the time is actually UTC.
tbh it was my first thought
If it wasn't then how would these ADS records look like for a 15 hour flight going through several different timezones ? Should you then use each lat/lon to determine the timezone, then calculate the absolute time to then calculate it back to local a timezone if you wish to work with the data from those records (for whatever purpose) ?
Imagine you have an airport located at the border of a timezone, the airport is busy and the plane circles around in its queue until it is it's turn to actually initiate landing sequence. Each and every time the plane circles around through different time zones. How would those ADS records look like if the epoch field was actually expressed in "local" time ? Pretty messy if you ask me