Dealing with datetime

[BenjiTheC]

@Trinkle23897 and I discussed about handling timezone related issues in the backend. The way we decided to handle it is a bit bizarre and requires detailed & on point documentation. This issue serves this purpose.

Current problem

The data in the production server is stored in specific structured files /visa_type/location/YYYY/MM/DD where DD is a text file that stores all the successful fetched results for the date YYYY/MM/DD (file-path) in the granularity of minutes. Each line in a DD file is composed in the format of hh:mm YYYY/MM/DD where hh:mm is the time the result is fetched and the string YYYY/MM/DD (file-line) is the fetched result of available appointment date. When looking at a given /visa_type/location/YYYY/MM/DD in the line of hh:mm YYYY/MM/DD, it tells you that "On the date of YYYY/MM/DD (file-path) at the moment of hh:mm, one can schedule an appointment on the date of YYYY/MM/DD (file-line) for {visa_type} Visa at the U.S. Embassy/Consulate in {location}.

If we describe YYYY/MM/DD hh:mm (file-path) as write_time and YYYY/MM/DD (file-line) as available_date (for Visa interview appointment). We have an issue here:

All of the write_time is in the timezone of UTC+8 offset where as all of the available_date are the date in the local timezone of a given U.S. Embassy.

Currently data in Mongo is stored in documents defined by Visa type, embassy code and the date of fetching, which is the time in UTC+8 offset. But in Mongo it assumes all datetime are in UTC standard time. To fix the issue, simply converted the write_time is not enough, specifically for the visa status overview.

The visa status overview contains the metadata (overview) calculated from the fetched data of a given fetching date (write_date), including the earliest available appointment date and latest available appointment date (minimum and maximum datetime). An new issue derives here: the previously calculated overview data compares all data fetched from 00:00 to 23:59 in UTC+8 offset. So if the new overview data is calculated from 00:00 to 23:59 in UTC+0 standard time, the new overview data will be different from the previously calculated one.

How to handle it for now

In the perspective of a user, when looking at the overview of available dates, what the user most likely cares about is when s/he goes to the country of the U.S. Embassy locates, what's the available date in the local time. So it seems reasonable to calculate the earliest and latest in the scope of 00:00 to 23:59 in the local time zone.

So here below is the solution for handling the timezone issue in the backend:

1. All of the available_date data are stored as is. (what we fetch is what we store)
2. All of the write_time and write_date data in Mongo collections visa_status and latest_written are stored in UTC+0 standard time.
3. (Very important here) All of the write_time and write_date data in Mongo collection overview are stored in the time in the local time zone of a given U.S. Embassy location. e.g. The overview data of U.S. Embassy in Phnom Pend on the date Oct 10th, 2020 stands for the time range "2020-10-10T00:00+07:00" to "2020-10-10T23:59+07:00", NOT "2020-10-10T00:00+00:00" to "2020-10-10T23:59+00:00".
4. All time data in a HTTP request from frontend must be a UTC standard time. The Date.toISOString is the default way we construct the time related query in a request url in frontend. FastAPI backend should add a layer of logic that consolidate the received datetime object must have a tzinfo attribute otherwise should return a 422 status code.