Is it always a good idea to store time in UTC or is this the case where storing in local time is better?

Sun_19917 0
Viewed 77119

Generally, it is the best practice to store time in UTC and as mentioned in here and here.

Suppose there is a re-occurring event let's say end time which is always at the same local time let's say 17:00 regardless of whether there is Daylight saving is on or off for that time zone. And also there is a requirement not to change the time manually when DST turns ON or OFF for particular time zone. It is also a requirement that whenever end time is asked by any other systems through API (i.e. GetEndTimeByEvent) it always sends the end time in UTC format.

Approach 1: If it is decided to store in UTC it can be stored in the database table as below. 

Event      UTCEndTime
=====================
ABC         07:00:00
MNO         06:00:00
PQR         04:00:00

For the first event ABC, end time in UTC is 07:00 am which if converted to display from UTC to local time on 1-July-2012 it will result into 17:00 local time and if converted on 10-Oct-2012 (the date when DST is ON for the time zone) then will result into 6 pm which is not correct end time.

One possible way I could think is to store DST time in the additional column and using that time when the timezone has DST ON.

Approach 2: However, if it is stored as Local time as below for example for event ABC it will be always 17:00 on any date as there is no conversion to from UTC to local time. 

Event      LocalEndTime
=======================
ABC         17:00:00
MNO         16:00:00
PQR         14:00:00

And an application layer converts local time to UTC time to send to other systems through (API GetEndTimeByEvent).

Is this still a good idea to store the time in UTC in this case? If yes then how to get a constant local time?

Related Questions: Is there ever a good reason to store time not in UTC?

utc dst timezone time date
7 Answers

Can't we always compute the local time given UTC and a timezone? We can't really reliably store a time and a timezone encoded in the time itself since the offsets for timezones can change and the ISO standard only allows us to encode the offset which could change. So, we can't, say, store a time in the future encoded in the local time zone since we don't actually know the offset yet! So, store times in UTC and store the timezone as a separate entry and compute this when needed which is less error prone. Local time is usually an implementation detail. It seems when we store this we are probably mixing up concerns. It's the business of the view to show time relative to timezones most of the time. By storing the components of things and allowing computations to compose them we gain the most flexibility as a general rule.

user_9513490
Related