Is it always a good idea to store time in UTC or is this the case where storing in local time is better?
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?
Answer
I think that in order to answer that question, we should think about the benefits of using UTC to store timestamps.
I personally think that the main benefit to that is that the time is always (mostly) guaranteed to be consistent. In other words, whenever the timezone is changed, or DST applied, you don't get back or forth in time. This is especially useful in filesystems, logs and so on. But is it necessary in your application?
Think of two things. Firstly, about the time of DST clock shift. Is it likely that your events are going to occur between 2 AM and 3 AM (on the day the clock shift is done)? What should happen then?
Secondly, will the application be subject to actual timezone changes? In other words, are you going to fly with it from London to Warsaw, and change your computer timezone appropriately? What should happen in that case?
If you answered no to both of those questions, then you're better with the local time. It will make your application simpler. But if you answered yes at least once, then I think you should give it more thinking.
And that was all about the database. The other thing is the time format used internally by the application, and that should depend on what actually you will be doing with that time.
You mentioned it exposing the time via an API. Will the application query the database on every request? If you store the time internally as UTC, you will either need to do that or otherwise ensure that on DST/timezone change the cached times will be adjusted/pruned.
Will it do anything with the time itself? Like printing the event will occur in 8 hours or suspending itself for circa that time? If yes, then UTC will probably be better. Of course, you need to think of all the forementioned issues.