I ran into this exact problem when working at 500px, a photo sharing site. There was a debate about how to store the date and time of each photo. Most just went with saying to store the UTC time and converting it for display. I argued against that since it would show a New Year's Eve photo as Dec 31 or Jan 1 at any hour depending on the timezone where you were viewing it.

The best solution I came up with was to store both the location and date/time as strings! We didn't have to do math on this data, sorting is pretty much by upload timestamp or upvotes. For display, we can just display the date/time and location if available. Heck they could just specify a location and leave the date/time blank, why not?

As a small optimalisation, for some applications it may also be acceptable to configure a timezone that applies to the whole database, and then to do everything else the author describes here.

The gcal tz picker truly is atrocious and I fail to understand how it has gone for so long without being improved.

I think I do not have a good mental model of how work is prioritised, done and shipped at Google because I cannot explain the current state of that modal.