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

Question

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?

Solution 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.

Solution 2

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.

Solution 3

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).

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[Michał Górny]

Will the program be used only for such a kind of events? Will it need to serve events occurring at the time of DST change? Should the program handle changing timezones?

[Sun]

@Michal Yes, all the events are always local time (i.e. 4PM or 9PM etc...) of particular local timezone irrespective to DST is ON or OFF. Lets say a particular event "MNO" happens in London 4PM local time on everyday of the irrespective DST is on or OFF.

[trench]

What about events which happen on DST dates during the hours of the switch? I have some data which happened on March 13th between 1am or 2am. I was getting an error when I tried to convert it to UTC and I do not know if the timestamps accounted for the the switch or not.

[Zero3]

I think you have a valid point, but I found that your tone of voice distracted me a bit from it. I think your answer would be more appreciated if you wrote it in a more neutral/formal way :).

[Peter]

The problem is that you can't go from either epoch or UTC date/time values back to the user's original local timezone date/time (without also storing additional information). Converting from a local timezone to epoch or UTC is a lossy transformation.

[prasanthv]

Any resources to share around this?

[Peter]

Hard to answer in 600 characters, so I'll have to point you to further reading instead. This SO question is a good start, and contains lots of links to further reading: stackoverflow.com/questions/2532729/…

[Peter]

Of central importance is understanding the problem domain and whether it contains physical times, civil times, or both. Epoch is passable for handling physical times (though I prefer UTC), but for the systems I've worked on civil time is typically much more common.

[user48956]

Lossy can happen when passing though floating point representations. For example longs holding x-since epoch are an excellent way to store time stamps, except in JavaScript where there are no longs, because, well, JavaScript. Truncation of the the timezone can also happen when you pass times I and out of timezone aware and unaware representations (looking at you Python sql drivers)

[oligofren]

Using non-UTC timestamps has several problems, too many to list in a comment, but I'll add a few links at the end. If you need the timezone of the timestamp you can store it in an additional comment. Then you get all the benefits of UTC while still being able to transform the timestamp to one capturing all the original data. - 2018.javazone.no/program/0507d823-bfa9-483c-8aef-3881c38c315‌​e - ideas.kentico.com/forums/239189-kentico-product-ideas/…

[Luke Puplett]

Jon Skeet posted today on this topic. codeblog.jonskeet.uk/2019/03/27/…

[jgmjgm]

Converting to UTC doesn't lose you anything from the point of view of the user. You can convert UTC to any timezone but you can't all timezones to UTC. This is why UTC is the standard and why it's called Univeral. Some people take it further and always store both time formats incase something went wrong (wrong timezone specified).

[AhammadaliPK]

So if I save DateTime as UTC DateTime(not as varchar, ToUniversalTime c#) in database , then when im populating , do I always need to convert it at client side to get the exact date ?

[Luke Puplett]

@jgmjgm Flat wrong. Sure, you can convert UTC to any timezone but you'd need to know what the DST rules were at the time, for all timezones. Good luck with that. Why? Because those rules change over time. Try your conversion for a time of a stock trade logged in 2006 timeanddate.com/laws/us/energypolicyact2005.html