1

I am setting up a webapp for a business which takes future bookings which can also be recurring. At this moment, only the business can make the bookings.

Business rules:

  • The app must save a timezone against the user based on the business address provided at account registration. All events will be taking place at the business address. UPDATE: following comments, this rule will be changed to: Timezone will be recommended to the user user using a geocoding service (in the instance that a match is found), but user must confirm their timezone.
  • When logging in from another timezone, the webapp app booking system should still appear to the user to be set to the business timezone. (Potential scenario for this is the user is at a meeting abroad, but still wants to check up on their bookings). This should mean that they can still use the system as before eg. by adding new bookings. However, I believe this would mean the system has to ignore the user's local timezone, and apply the business timezone to any booking events.

I have the booking system working for a user that is within the same timezone. But when they change timezones the future bookings calendar breaks.

My app is nodeJS with postgresql. What I am confused about is what I should be persisting with regards to datetimes, and what relevant conversions to perform.

I think it would be prudent to save the client locale datetime always (even if not used as requirements may change), and also the UTC time. But I think I will have to also construct a date (in javascript) that takes the datetime selected and applies the business timezone to this (using eg. moment.tz.setDefault("America/New_York")?). I am unsure what to do about receiving this date on the client when viewing the scheduling information. How do I ensure that it displays with the business timezone?

Milambardo
  • 330
  • 2
  • 14
  • It's not possible to reliably determine the offset rules based solely on address. Currently, the most common way to apply rules is to use IANA representative locations, however as far as I know they do not have associated geographic boundaries to determine the precise region that uses a particular set of rules. You might guess the location to use from the address , say based on nearest IANA location, then ask the user to confirm and store that as the "timezone" in your database. Store dates as UTC, then convert to a particular timezone for presentation. – RobG Sep 10 '20 at 22:29
  • You can display a date for a particular timezone using [*toLocaleString*](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleString) (which leverages [*Intl.DateTimeFormat*](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/DateTimeFormat/DateTimeFormat)), e.g. `new Date().toLocaleString('default',{timeZone:'America/New_York'})`. There are lots of existing questions and answers about that. :-) – RobG Sep 10 '20 at 22:32
  • @RobG - Geocoding services like those from Google and others can map address to lat/lon. [Time Zone Boundary Builder and libraries that use it](https://stackoverflow.com/q/16086962/634824) are reasonably reliable to get time zone from there. – Matt Johnson-Pint Sep 11 '20 at 04:59
  • @MattJohnson-Pint—I'm sure those services are right 99.9% of the time, especially in urban areas, but no address lookup service is reliable (I have a property in a major city that's had its current address for 2 years yet does not appear on any online site I've tried to verify it with). Also, the rules applicable to a particular place can change frequently, especially for more remote areas with a strong sense of autonomy. There are places that have no coherent address system, e.g. Saudi Arabia, though likely country name is sufficient in that particular case. – RobG Sep 11 '20 at 06:34
  • Thanks for the comments. I'll update the question to state that timezone will be recommended where possible (through use of Geocoding services mentioned) but that user must confirm. – Milambardo Sep 11 '20 at 13:23
  • 1
    @RobG - Agreed on all points. TBB is the best data we have though. The nice thing about it is that it references OSM for most of its boundaries - so as disputes change borders, those changes get reflected back in the data. The geocoding part is, of course, more difficult. – Matt Johnson-Pint Sep 11 '20 at 15:57

1 Answers1

2

You should always store the time zone that is relevant to the event. Since you said "All events will be taking place at the business address", then the time zone of the business is relevant.

If these are physical in-person events, then the user's current local time zone isn't relevant at all. For example, I would expect that if I made a dentist appointment in Los Angeles, that I would specify the time in Los Angeles even if I was making the appointment from New York.

However, if the events are virtual online events, then you may also want to add some logic in the UX to show both the time in the target time zone and the equivalent local time. For example, if I'm scheduling a video call next week with a person in Los Angeles, and I happen to be in New York today, you have no idea whether I will still be in New York at the time of the call or if I'll have travelled to Los Angeles by then. You should allow me to choose which time zone I'm creating the appointment in, and show both the time there and my equivalent local time.

The time zone itself should be stored as a varchar(14). IANA 2017c established a 14 character limit for individual segments of zone or link identifiers, removing the only link name that exceeded it, Canada/East-Saskatchewan. (If you have older data that includes that, replace it with America/Regina.)

For a single appointment, you should store the date and time of the appointment, in the time zone of the appointment. You should store this in a data type that holds the date and time without any time zone or offset. timestamp without time zone in postgres, datetime2 in MS Sql Server, etc.

You might think you should use a timestamp with timezone (or datetimeoffset type in SQL Server), but that would pin the appointment to the equivalent universal time, using the rules applicable at the time the appointment was scheduled. If those rules change before the appointment comes into effect, then the appointment will shift to a different local time. This is usually undesired. The point is to capture the intent of the user. In other words, if I say "8am on December 1st", then I mean that regardless of what my government does to my time zone between now and my appointment time.

You might also store the UTC time equivalent in a separate field, but you'd need to recompute it any time the server's time zone data was updated. (Such a field can be convenient to quickly query for upcoming events.)

For a recurring appointment, all the above still applies except that you'll need to store some form of recurrence rule. Some people like to store many fields for various components of the rule (such as one field for "daily", one field for "on Wednesdays", one field for the time of day, etc...). You can use the date and time types in your database if you like. Other people like to store a string with a chron expression. It really just depends on your needs.

It's especially important with recurring appointments to consider that the UTC equivalent for each occurrence will not necessarily be the same. Consider that many time zones alternate their UTC offsets based on whether daylight saving time is in effect. For example, if I'm creating a daily appointment in Los Angeles at 10 am, that would be 6 pm UTC during Pacific Standard Time, and 5 pm UTC during Pacific Daylight Time. Thus, you cannot just store the UTC time. Again, capturing the intent of the user is the most important part.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575