I have users from multiple timezone and storing time in Database in UTC format. Running into issue with time part alone.
Storing user working hours for the Week as follows.
| Id | UserId | Day | StTime | EndTime |
|---|---|---|---|---|
| 1 | 1 | 0 | 10:00 | 18:00 |
| 2 | 1 | 1 | 10:00 | 18:00 |
| 3 | 1 | 2 | 10:00 | 18:00 |
| 4 | 1 | 3 | 10:00 | 18:00 |
| 5 | 1 | 4 | 10:00 | 18:00 |
Day, 0 for Sunday,1 for Monday, ...
Here, the time is being stored in UTC. The time is being converted in the UI based on the browser timezone. Since, there is no date part needed here, it is causing issues here.
For ex, Let's say user is selecting 7AM to 7PM in UI and the time gets converted to UTC which is 15:00 today to 02:00 tomorrow. So, in DB the time is being stored as
| Id | UserId | Day | StTime | EndTime |
|---|---|---|---|---|
| 1 | 1 | 0 | 15:00 | 02:00 |
| 2 | 1 | 1 | 15:00 | 02:00 |
| 3 | 1 | 2 | 15:00 | 02:00 |
| 4 | 1 | 3 | 15:00 | 02:00 |
| 5 | 1 | 4 | 15:00 | 02:00 |
Here, start time is bigger than the end time, which is not correct.
How do we need to handle timezone when storing just the time part of it?
My requirement is to get open slots for a user based on the following info.
BookedSlots,
| Id | userId | St_time(datetime) | duration(mins) |
|---|---|---|---|
| 1 | 1 | 2021-11-27 16:00:00 | 30 |
| 1 | 1 | 2021-11-27 18:00:00 | 45 |
Here, the time is in UTC.
| Id | userId | St_time | end_date | st_time | end_time |
|---|---|---|---|---|---|
| 1 | 1 | 2021-11-22 | 2021-11-25 | 09:00 | 18:00 |
| 1 | 1 | 2021-12-17 | 2021-12-20 | 09:00 | 18:00 |
Here also, time needs to be stored in UTC
| Id | UserId | Day | StTime | EndTime |
|---|---|---|---|---|
| 1 | 1 | 0 | 15:00 | 02:00 |
| 2 | 1 | 1 | 15:00 | 02:00 |
| 3 | 1 | 2 | 15:00 | 02:00 |
| 4 | 1 | 3 | 15:00 | 02:00 |
| 5 | 1 | 4 | 15:00 | 02:00 |
I need to calculate open slots based on the above 3 info. But, time part is confusing when we store that in UTC. What is the best way to handle this?
One way to avoid the midnight wrap-around would be to store the working hours as having a start time and duration, instead of a start time and end time, just like you are doing for the booked slots. You could do the same for blocked slots, so that you have a nice consistent storage model for all of your events.
I would also suggest storing the working hours in the user’s local time, along with an IANA timezone string indicating the work location. The problem with storing in UTC is that some time zones will enter in and out of Daylight Saving Time throughout the year. Let’s say I’m in New York City, and I want my working hours to be 09:00 to 17:00, New York time. Since it is Eastern Standard Time (UTC-5) in New York at the moment, that would be converted to 14:00 to 22:00. Once the summer comes, New York will enter Eastern Daylight Time (UTC-4), so my working hours will then suddenly appear to be 10:00 to 18:00 New York time.
You could also store the time zone with the user information, so that working hours are agnostic to the time zone. This would allow you to specify something like “09:00 to 17:00 in whatever time zone the user is in.”
Any time you are dealing with recurring events, I always find it’s more clear to store them in the time zone in which the events will occur, since the logic for converting from UTC changes throughout the year. For absolute events, like booked and blocked slots, it still makes sense to keep them in UTC, since they are referring to one particular event in time.