(06-16-2018, 01:23 PM)Unknown98 Wrote: The issue is calculating the correct dates for arrival and departure, especially several days in advance. Working with time zones, different DST settings, crossing the international date line, all makes scheduling very complicated in the back-end for a real-time system. Routes are created by users in local time, but then I have to convert that to UTC time, and add/subtract days to departure and arrival times.
Understand that this is complicated and appreciate your efforts in ironing out all the issues.
I think to simplify things, you should be storing the times of the routes and flights in UTC / GMT+0 or server time. Whichever it is, it needs to be consistent across.
That means:
- Stored all time in UTC / GMT+0 in routes database.
- When a route gets creates, convert the time to UTC / GMT+0 before storing.
- When a flight gets generated, departure and arrival times are stored in UTC / GMT+0 in the back end, regardless of the origins and destinations.
- GMT time zones get stored per airport, in the airport database. You probably want to separate this into different columns: normal time, DST, DST start date, DST end date, and potentially a DST? boolean.
- When a route or a flight gets displayed, regardless of page, it queries the airport database for airport time zones. Then display the departure and arrival time in local airport times based on that.
This might add to the server load, dependent on what setup you have currently. However, this would definitely solve your issue above. At the same time automates all the required adjustments of flight schedules due to DST, which would happen if times of routes are stored in local time. (Think from a gate use perspective, if flight A use the gate until 40 minutes before flight B, but flight B originates from a DST airport while flight A doesn't. When DST comes in effect, flight B will arrive 20 minutes before flight A is scheduled to depart if departure times are stored in local time, creating problems. Similarly, from a route perspective, in a roundtrip itinerary, if one airport has DST while another doesn't, it could screw up the whole itinerary when DST comes into effect, or comes out of effect, if the plane has less than 1 hour idle on the ground and you store departure time in local time of departure airport. If you store all routes and flights in GMT+0, the local time of the flights will be re-timed automatically when DST comes into and out off effect.)
This also leads to two even more complicated questions down the road: time-padding of itinerary, and flight delays (more specifically, systematic delays related-to DST due to airport slot restrictions). But these don't need to be considered now; not until all the fundamentals are sorted.