Tuesday, January 30, 2024

Support for local time on import

I've received many requests over the years to support local time when importing flights, particularly for airline pilots who have schedules that are provided in local time.

I've always pushed back on this because it's a genuinely hard problem to solve; see my earlier post about what was required.

Alas, perhaps I just needed to look harder.  I finally found the right google queries to lead me to a free library that will convert a latitude/longitude to a time zone descriptor that is sufficiently narrow that a second library can convert local time to UTC with reasonable reliability.

Finding the right tools was absolutely the hard part; integrating them was somewhat easier, so now on import there is an option that allows you to tell MyFlightbook that your input times (block out/in, engine start/stop, or flight start/stop) are in local time rather than in UTC.

I still advice using this option with care.  Allow me to explain why.

I can't do any meaningful computation on local times; everything has to be UTC for autofill to work.  Think about it: Boston to Chicago is a 2-hour flight by jet, but Chicago is an hour behind Boston.  So if you leave Boston at 10am, you arrive at 11am; if you leave Chicago at 10am, you arrive in Boston at 1pm.  Using local times would imply that it's three times as long to go eastbound than westbound!

But how and when do I convert from local to UTC?

  • First, if you have a flight path (GPX, KML, CSV recorded by the app), then I already have time-stamped UTC data and I don't do any local time conversion.  There's no need - the flight path tells me everything I need, so the block/engine/flight times you provide can be safely ignored
  • If you don't have a flight path, I look at the airports in your route.  If I don't find EXACTLY two airports that match distinct airports in the database, then I can't determine which airport was when, so I cannot do any local time conversion.  In this case, minimal autofill will happen, but you could still find some wonkiness: if you didn't specify a total time, then I will compute it as if everything were UTC.  So in the Chicago example above, you might get 1 hour or 3 hours instead of 2.  (If you've provided a total time, though, then it won't be overwritten).
  • If you do, however, have exactly 2 airports in your route, then the system will:
    • Compute the UTC time at departure and destination, using the airport's latitudes/longitudes and the block/engine/flight times as appropriate, converting them from their respective local times.
    • Construct a synthetic great-circle path from A to B and evenly space out the samples across it, both in space and in time.
    • Clear the provided Total Time and Night Time fields (and a few others) so that they can be recomputed from the path.
    • "Fly" the synthetic path to determine things like night and total time, filling them in and computing engine start/stop, in UTC, based on the start/end of the path 
All of the last step above is the same as what happens when you provide UTC dates, except for the conversion from local time, but that's where the risk lies.  It's possible for these conversions to fail for a variety of reasons:
  • The lookup of the time zone from the latitude/longitude may not hit a time zone
  • The time zone returned by the lookup may not be found by the system that converts from local time
  • Time zone boundaries and rules (especially daylight time!) change over time and may not be reflected correctly
  • There are (rare) corner cases that are fundamentally problematic.  E.g., 1:30am on the date that DST ends is ambiguous because it occurs twice, and it is problematic on the date that DST begins because it doesn't happen at all!!
I should note that I decided against (at least for now) supporting local-to-UTC conversion when you press Autofill on the website or on the mobile apps.  The mobile apps were actually a really easy decision: you're not importing local times on the mobile apps.  They're either collecting data in real time (and they know their offset) or are working from GPX/KML files, which are all UTC.

But the website gets more complicated because you can specify a "preferred time zone", and that makes things very confusing.  Note that a "preferred time zone" is NOT a local time zone.  I live in Seattle, and I use Pacific Time as my preferred time zone.  All the preferred time zone means is that UTC dates are converted to and from PST for display.  So when I go out and shoot approaches from 10am to 1pm (=1800Z-2100Z, at least this time of year), it's stored under the covers as 1800Z-2100Z, but it's displayed as 10am-1pm.  

But there are at least two places where preferred time zones and local time zones collide.  One is if you have a flight that crosses time zones.  So if I fly from Boston to Chicago from 10am EST to 11am CST, that's stored under the covers as 1500Z to 1700Z, and it will be (correctly!) displayed for me in my preferred time zone of PST as 7am to 9am.  Notice that the preferred time zone is consistent across both departure and arrival!  So this gets to another problem which is that there's no easy way to indicate a non-preferred time zone for one field vs. another when doing data entry - they're all in one time zone.  So there's really no good way in the UI to indicate "local" time vs. preferred-time-zone time, much less departure-time-zone vs. arrival-time-zone.

The other is that the conversion to/from preferred time-zone is done "right now".  As I type this, it is January of 2024, so Pacific Time is 8 hours behind UTC.  Super - if I put in 10am, then under the covers it will become 1800Z.  But if I do this for a flight back in July, then it will do the same thing, but the time then was of course 1700Z because of DST.

That's probably all way more detail than you wanted to know, but I wanted to share some of the complexities here.

Enjoy the new feature - with care! - but it's still best to do everything in UTC.