Time handling in application handling multiple timezone

Question

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.

1. Booked slots
2. Blocked Slots
3. Working hours

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.

2. BlockedSlots

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

3. WorkingHours

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?

Roselynroseman

Can you expand further? It's not clear to me what the actual issue is. There is not start time in the items listed at 15:00; where the browser time conversion code? – Emergence

@Emergence Updated the question – Roselynroseman

"Here, start time is bigger than the end time, which is not correct." - Ah, but you see, It is correct! You are using UTC so, in UTC, those are the correct start and end times. – Emergence

I think the difficulty is that you are storing only times and not datetimes - if you are going to use UTC, you should be using datetimes since the entire piece of information (including the day) is relevant. Otherwise, you will have to store a Day for StTime and another Day for the EndTime – Emergence

It sounds reasonable. Is this the best approach for this use case? – Roselynroseman

If I were you, I'd store full date times (in UTC) - that way you have all the correct information in one field (well, two: start and end timestamps) - On the UI, you can display it according to the user's timezone. For calculations, it is easier to grab the time stamp, convert it into a number (long) and do math with two dates (i.e. two long numbers) then displaying the formatted dates. – Emergence

Thats how it should be. But for user working hours, how can we store date part as well as it is by day not by date. – Roselynroseman

there is no best approach per se, as it really depends on your requirements – Emergence

Let us continue this discussion in chat. – Roselynroseman

Can't you just add one day if start time is greater end time? Otherwise I think this gets kind of complex as you mix working hours which are intrinsically local time (and not date-specific in a sense) with time zones (which are date-specific! Not even sure if using UTC here is best). – Sacring

Are those working hours recurring? I mean does your working hours (for example) denote from 10 to 18 on this particular day or do they mean from 10 to 18 every Monday? I’d register those two in quite different ways. – Encounter

@OleV.V. Its recurring. Its every monday, tuesday, ... – Roselynroseman

In that case you need to store the local time and the time zone. 7AM local time doesn’t translate to the same UTC time during DST (summer time) as during standard time. For time zones that use summer time, but I suppose that you cannot rule out that some user lives and works in such a time zone. – Encounter

@OleV.V. This is a great info. But, storing datetime should not have this issue. For appointment, we use datetime. For ex, appointment on Jan 10th at 7 AM or Appointment on June 15th at 8 AM, storing this as datetime in UTC should not have this issue. – Roselynroseman

Answer 1

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.

Silden

Answer 2

If you want the endTime > startTime

you can do this kind of calculation

sTime = StTime
eTime = StTime + mod(EndTime-StTime+24hour, 24)

For example:

StTime=15
EndTime=02

sTime = 15
eTime = 15 + mod(2-15+24, 24) = 26

which 26 means (24+2) => next day + 2 hours and

StTime=09
EndTime=18

sTime = 09
eTime = 09 + mod(18-09+24, 24) = 18

the 09 to 18 will remain the same

calculate the datetime in Postgresql

SELECT TO_TIMESTAMP('2021-11-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS') +StTime AS stime , TO_TIMESTAMP('2021-11-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS')+StTime+(MOD((EXTRACT(EPOCH FROM (EndTime - StTime)::interval)+86400)::int4, 86400) || ' second')::interval AS etime FROM WorkingHours

you should be able to get the start and end datetime which end > start

a result example

UserId  StTime      EndTime       stime                   etime
1       15:00:00    02:00:00      2021-11-26 15:00:00     2021-11-27 02:00:00
2       09:00:00    18:00:00      2021-11-26 09:00:00     2021-11-26 18:00:00

hope this could help you

Finny