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: https://stackoverflow.com/questions/1127781/is-there-ever-a-good-reason-to-store-time-not-in-utc?rq=1

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.

I like to think of it this way:

Computers don't care about time as a human-understandable representation. They don't care about time zones, date and time string formatting or any of that. Only humans care about how to interpret and represent time.

Let the database do what it's good at: storing time as a number--either a UNIX epoch (number of seconds elapsed since 1970-01-01) or a UTC timestamp (no timezone or daylight saving time information). Only concern yourself with representing time in a human-understandable way when you must. That means in your application logic, reporting system, console application or any other place a human will be viewing the data.

The following wouldn't apply for a truly multi-tenant global SaaS product, so this opinion is aimed at simple "Line of Business" app developers.

Storing as UTC is fine but there is one requirement that causes pain if you do this: "Can you write me a report that shows me how many of X that occur per day?"

If you store dates as UTC, this requirement will cause pain; you need to write timezone adjustment code on the application server and in your reporting; Every ad-hoc query you perform on data that includes date criteria will need to factor this in.

If you application meets the following criteria:

1. Each instance is based in a single timezone.
2. Timezone transitions are usually outside office hours or you don't really care about "durations" of things to the level that a missing hour or so will matter.

I suggest you store the datetime as local date time, whilst using a library that isolates you from server timezone config issues (e.g. Noda.Time in the world of .net).

If your inter-system messages use ISO 8601, and your database is storing the origin local time + offset (like datetimeoffset in MSSQL or ISODate in Mongo as ISO 8601 captures it) and you're only using DateTimeOffset in .NET or OffsetDateTime in Java or some equivalent in your code, then no conversions are needed, at all. You just store it. All comparison functions will just work.

If you convert to UTC in your persistence, then you've lost the offset from the point-of-view of the user. Displaying when your user signed a document a decade ago is now a hard problem. Working that out from UTC will mean looking up the DST rules that were in play at that time in that territory. Nightmare.

I believe the reason we are all so used to converting to UTC for persistence is because we never used to have the right datastructures/data-types to allow us to do the right thing.

I would just store the Time component only without any Zone. Whenever the API has to serve it, add the correct date and convert that as local time to UTC for that date.

Database = 17:00 (use a timestamp without date, hours as byte, minutes as byte, string)

Retrieve = Date where we want the event + Database 17:00 => Convert this from local to UTC

This way you will always serve the correct time in UTC.

You're actually not storing a specific point in time as most time APIs assume. Use intervals if your database supports it (PostgreSQL does) or store it as an integer representing the number of seconds (minutes/hours) since midnight or corresponding beginning of the schedule (Monday, the first of the month, etc). In either case, you've dropped a lot of the headaches of worrying about how "Time" is handled between systems, and added only a very minor headache of converting seconds to time of day in your view.

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.