1

This is a question similar to this one.

I'm really tempted to store datetimes in my app as local time rather than as UTC (which is considered a best practice). In the app I have a number of events happening, each assigned to a given location. Always when I display them to the user, I want to show the local time of the event. I.e.:

====================================================================================
Event time (with TZ)   | As UTC           | As local time    | To be displayed  |
====================================================================================
2014-01-15 22:30 GMT   | 2014-01-15 22:30 | 2014-01-15 22:30 | 2014-01-15 22:30 |
2014-01-15 23:30 GMT+1 | 2014-01-15 22:30 | 2014-01-15 23:30 | 2014-01-15 23:30 |
2014-01-16 00:30 GMT+2 | 2014-01-15 22:30 | 2014-01-16 00:30 | 2014-01-16 00:30 |
====================================================================================

If I decide to store the event times in UTC:

  • it will be difficult to display them (because with each event I need to have the event's timezone and format the date for that timezone).

  • it will be very difficult to query them (i.e. if I want to show all events that happened on 2014-01-15 local time I need for each event to compare that event's time with what '2014-01-15' means in that event's timezone. Not sure if this is even possible in SQL...)

If I decide to store the event times as local times:

  • I will not be able to compare times of events for different locations (but this is ok for me)

Since in the vast majority of cases in the app I'm interested in the local time (usually displaying the, so called "television time") and there aren't many cases where I'm creating new datetime objects (for which I need the location timezone), I believe saving datetimes as local time is justified in this case.

Do you think it is? What are other downsides of storing local times?

Thanks in advance for your attention and help.

Community
  • 1
  • 1
machinery
  • 3,793
  • 4
  • 41
  • 52

1 Answers1

5

The most commonly overlooked case of when not to use UTC is for scheduling of future times - especially in recurrence patterns.

Imagine if you're alarm clock was scheduled by UTC. Say you set it for 7:00 AM daily. On the day after a DST transition, you'd either wake up at 6:00 AM or 8:00 AM depending on which direction the transition was.

Also, the rules by which we determine time zone offsets and daylight saving time changes get updated all the time! So you can't take some future local time and convert it to UTC without also retaining the local time itself. Otherwise, when things change, you won't have an original source of truth and again all your times will be off.

I've posted on this many times before.

Of course, once the event has occurred, then you certainly want to record the time either at UTC, or using a DateTimeOffset.

Another common use case is for dates without times, especially birth dates and other anniversary dates. These should always just be stored as year, month, day (such as in a date field type in most databases) - with no conversion between time zones or UTC.

Regarding your specific points:

If I decide to store the event times in UTC ... it will be difficult to display them...

That is quite easy actually. Almost every programming environment can do this easily. The only place this is difficult is in JavaScript for non-local time zones, and there are libraries to cope with that.

If I decide to store the event times in UTC ... it will be very difficult to query them (i.e. if I want to show all events that happened on 2014-01-15 local time I need for each event to compare that event's time with what '2014-01-15' means in that event's timezone. Not sure if this is even possible in SQL...)

That is true. Everybody's "today" is different. If you need data tied to a floating "day" then that is another case for not storing in UTC. However, a DateTimeOffset would give you the advantages of both.

If I decide to store the event times as local times ... I will not be able to compare times of events for different locations (but this is ok for me)

It's more than that. A local DateTime without an Offset may be ambiguous to to daylight saving time fall-back transitions. So you could have a single local value that you can't distinguish which of two points in time it corresponds to.

Regarding "television time" - reserve that concept for things that are truly "floating". Example: A company has offices all over the world that all start at 8:00 AM. That's a floating time.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Thank you for the valuable insight. You mentioned that DateTimeOffset would give me the advantages of both. How is this possible if the query is working on the DB level and DateTimeOffset is on the app level? Do you mean getting more rows in SQL and then filtering them out on the app level? – machinery Jan 15 '14 at 10:23
  • In Postgres, you can use a `TIMESTAMP WITH TIME ZONE` data type. You could then have visibility over *either* the local value (as-is) or the UTC value (after the offset is taken into account). However, this is much easier with SQL Server's `DATETIMEOFFSET` type. Postgres might get in your way a bit. – Matt Johnson-Pint Jan 15 '14 at 17:25
  • And if you plan to have a lot of data, then you would be better off with separate fields anyway, so you can index by a local date even if you're storing a UTC timestamp. So then you would use `TIMESTAMP WITHOUT TIME ZONE` for the UTC portion, and probably just `DATE` for the local date value. – Matt Johnson-Pint Jan 15 '14 at 17:26
  • Also, make sure you read through these parts of the Postgres documentation: [here](http://www.postgresql.org/docs/9.3/static/datatype-datetime.html) and [here](http://www.postgresql.org/docs/9.3/static/functions-datetime.html). – Matt Johnson-Pint Jan 15 '14 at 17:28
  • This is in essence an open question, but I'm marking this as the answer because it's the only answer and it's quite helpful as well :) – machinery Mar 05 '14 at 07:04