Should future time be stored in UTC or local time

database-designdatetime

Clearly in most cases time should be store in UTC. And in all cases a datetime that records a past event should be stored in UTC. We do this because civil time keeps changing and an event happened at a moment that's detached from whatever the local government thinks the time is.

But when times are detached from dates, when they refer to wall-clock times, I contend that they should be stored in wall-clock time.

Let's say I have a system that has to perform an event at 3pm in a particular time zone, I contend that I'm best to store the time as 3pm. This is even more true when the event is recurring and should always occur at 3pm — whether daylight savings is in effect or not. And if it should occur for multiple users in various time zones at 3pm-local, storing anything in UTC just seems silly.

To me, this feels obvious. But I'm looking for a standard, a specification, or at least a blog post from a trusted source that can back me up.

Best Answer

In general I agree with your appraisal. When a time is going to be used under any of the following circumstances using local time only is appropriate:

There is some other bit of context that makes the time zone obvious or irrelevant.
- The time is going to only be used in reference to a local event like a concert. Since I know the concert is taking place in venue X it would be weird if the time wasn't in the time zone for venue X. Similarly it would be weird if my doctor's appointment was made based on a different time zone than the location of the appointment.
- I shouldn't have to set a time zone to get the alarm on my phone to go off at 6 o'clock in the morning. I should be able to say 6 am, and the phone should wake me up everyday at 6 am local time. Note I'm just talking about a basic alarm like you would have with a clock radio. I'm not talking about scheduling a meeting/appointment with people in different time zones.
For some reason the fact that X occurs on a regular interval is more important that X occurs at a specific time.
- Something needs to happen once a day and it doesn't really matter when it happens. Usually you would like to do some scheduled task during non-peak hours, but it is conceivable that this isn't always important.

Assuming the above is true then I'd fully support using local time. You didn't mention the database platform, but if your database platform supports it I think an event that isn't tied to a date should be stored in a pure time data type, assuming your database platform supports/offers this data type. For instance in SQL Server, I'd use the Time data type to store the hours of operation for a store. Using SQL Server once again, I'd use the DateTime2 data type to store the date and start time for a concert.

Since we're on the subject I personally would use UTC time on the server for everything that doesn't fit in the "local time only" parameters I outlined above, like you stated in your question. Many database platforms offer an analog to SQL Server's DateTimeOffset but this isn't really a safe solution because you end up losing the actual time zone information in lieu of the offset, and offsets change. For a more detailed overview on this and other time related issues, from a .NET point of view, check out this article written by Jon Skeet.

Try this test:

mysql> select
    -> timediff('2015-09-12 18:47:15','2015-09-12 18:38:33') as timediffA,
    -> timediff('2015-09-12 18:10:04','2015-09-12 16:34:05') as timediffB,
    -> time_format(timediff('2015-09-12 18:47:15','2015-09-12 18:38:33'),'%h:%i:%s') as time_A_wtimeformath,
    -> DATE_FORMAT(timediff('2015-09-12 18:47:15','2015-09-12 18:38:33'),'%h:%i:%s') as time_A_wdateformath, 
    -> DATE_FORMAT(timediff('2015-09-12 18:47:15','2015-09-12 18:38:33'),'%H:%i:%s') as time_A_wdateformatH, 
    -> time_format(timediff('2015-09-12 18:10:04','2015-09-12 16:34:05'),'%h:%i:%s') as time_B_wtimeformath,
    -> DATE_FORMAT(timediff('2015-09-12 18:10:04','2015-09-12 16:34:05'),'%h:%i:%s') as time_B_wdateformath,
    -> DATE_FORMAT(timediff('2015-09-12 18:10:04','2015-09-12 16:34:05'),'%H:%i:%s') as time_B_wdateformatH;
+-----------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| timediffA | timediffB | time_A_wtimeformath | time_A_wdateformath | time_A_wdateformatH | time_B_wtimeformath | time_B_wdateformath | time_B_wdateformatH |
+-----------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 00:08:42  | 01:35:59  | 12:08:42            | 12:08:42            | 00:08:42            | 01:35:59            | 01:35:59            | 01:35:59            |
+-----------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0,01 sec)

mysql>

Try it in SQLFiddle.

Best Answer

Related Solutions

Sql-server – Advice needed for Querying DateTimeOffset data by end users

MySQL Datetime – Calculating Time Difference

Try this test:

Related Question