How should I handle timezones in this context?

Question

I have an app that allows the user to create appointments that will always have future dates. I plan on having in the database created_at and updated_at columns for multiple tables. I am also planning to store everything in UTC since I have read is the best practice and allows me to query/filter the data without problems. To show the user the correct time for the appointment I was thinking I could store the offset so if there is any change in the rules the user will still see that the appointment is at 1:00 pm for example. I think my logic is correct for the given context but I want to know if there is something I am not considering that will cause problems. And if this is the right approach what advice do you have?

Answer 1

For most applications, I store the user's timezone as a preference, or in some case, I associate a time zone with a facility (as I did with a factory, for instance).

It's not clear whether your app is desktop, mobile, or web, but your desktop/mobile apps will generally want to convert using the device time zone (not offset).

For web, you can retrieve the browser's timezone offset via a simple line of JavaScript, however, keep in mind that there are lots of boundary conditions for which the offset is inadequate to calculate the correct time; to avoid those, you really need a full time zone name. There's a way of retrieving the client time zone name described elsewhere on SO. If you only use the offset, you may run into issues around daylight savings time/winter time/summertime boundaries, especially if you are dealing with ranges of datetimes. The date + time zone name together can be cross-checked against a proper time zone database, which your OS is likely regularly downloading anyway.

Answer 2

If you are handling only one timezone, it is much better not to use UTC. You got what best practices describe as wall-clock time, so the same time you will read on a clock on the wall. So you should just be consistent on that but so you simplify all summer times, time zones (of client, browser, etc.). But also you do not need a crystal ball, to know the timezone and summer time changes in future (some countries do it on a very short term, and operating system may take time to update the system, creating chaos). So really less confusing.

Note: sometime you will find the concept of naive, but it is not the same. With wall-clocks times you can use naive times and it is recommended. But naive times may imply a timezone (just the timezone and its logic is not on the time field).

As example: you do a appointment tool for a doctor: she will tell patients to come a 8 am. And if she will transfer to an other state (different timezone), you do not want to shift all times (e.g. opening hours). If she opens at 8am, probably she will open at 8am at new location. (wall-clock time)

But if you need to work with different timezones and the appointments may be coordinates, you need to store the timezone (so as aware instead of naive data type). And so you have much more complexities.

But also in such case, I would not use UTC. Store the dates in the expected timezone (of the practitioner): easier to check on the database, but there should not be problems on changes of future summer times. Sorting order may change in future, but usually it should not be a problem. Tools should be able to handle aware times without problems.

UTC times may be useful on timestamps when you may need to compare different timestamps (and usually as human, not computers which should be able to translate them easily). Or for military operation (where a unified Zulu time is less confusing). Or protocols where systems (routers) may never been updated. Else: think as an user. How the backend (so e.g. database) will store the time zone aware data should not concern us. Let's abstract and skip the implementation details.