15

Developing a web application which should allow User to schedule appointment based on their TimeZone. And I am storing the User scheduled datetime as server datetime into database field. While showing schedule information retrieved the value from Database and converted into user timzone. processing in Code base I am converting the DateTime based on the user timezone. Please suggest is this best practice or any easy way is exist?

33

Welcome to one of the hardest problems in non-computational programming - properly representing dates and times to end users.

Realistically, timestamps should be stored in a fixed single representation regardless of how they will be interpreted, because no matter how hard you try, you will always have ambiguous cases, and you can't resolve them without a fixed representation. And you've picked one of the worst of the use cases - scheduling an appointment. The only worse common use case is air travel, where a trip might start in one time zone and end in another, possible at an earlier local time.

Always, always, ALWAYS store in UTC and display in the user's preferred or explicitly specified timezone. If at all possible, make the user tell you what timezone they believe the timestamp to be in when they input it (e.g., have an explicit timezone field and pre-populate it with their preferred zone).

  • 1
    +1. And its flexible. Having a common, consistent, universal standard reference time- any local zone "dereferencing" will be done correctly. – radarbob Aug 26 '13 at 12:50
  • Actually, scheduling an appointment is one of the few times when you might not store it as UTC: If the DST rules change (or the offset from UTC), what happens to the appointment time? In most cases, you want the appointment time to stay the same locally, which means the UTC value changes. You'd most likely want to store a value representing "TimeInTimeZone+TimeZone", from which you could derive UTC easily. Cross-timezone scheduling (like airlines) has constraints that probably means a combination of both get used. – Clockwork-Muse Sep 10 '16 at 6:52
  • 1
    Oh, it gets worse than that. I regularly have meetings with colleagues in other nations whose time-change rules differ from mine. When that happens, what's the right answer? Actually, no computer can know :-( – Ross Patterson Sep 13 '16 at 0:40
3

The easiest way to deal with date/time information depends on the requirements of your application.

If the date and time are entered by the user and the server does no processing with that date/time information except to store it in a database and there is no expectation that the entered time gets adapted to the location where it is viewed (for example, the user entered "8:00 PM" and it should be displayed as "8:00 PM" regardless of where on the world it is viewed), then the easiest way is to store the DateTime as local time without any timezone information.

On the other hand, if the server must use the entered DateTime as a trigger to do something, or if the time should be correctly adjusted to the current timezone, then your best option is to store the DateTime as UTC time and adjust it to local time (for the user's current location) each time you read it from the database.

  • -1 for "store it in local time", +1 for "store it in UTC". – Ross Patterson Aug 25 '13 at 19:30
  • @RossPatterson: Can you explain your '-1 for "store it in local time"'? I am curious why it should be a bad idea, given the conditions I gave with it. – Bart van Ingen Schenau Aug 27 '13 at 6:43
  • 1
    For example, using user-local time means that every value is effectively one of several dozen different data types. Which means that you can't interesting perform database operations on them like "TIMESTAMP > '2013-08-27T12:00:00'". It even means you can't know when and if to apply Daylight Saving Time conversions. – Ross Patterson Aug 27 '13 at 11:00
  • @RossPatterson: Thank you. I agree that local time is not the right solution in most cases. – Bart van Ingen Schenau Aug 27 '13 at 16:05
2

It is important that you do not conflate two related concepts.

If you are manipulating an actual point in time, that is a specific time on a specific day, then the only way to do this is to always use the universal UTC time value. Never attempt to store this as a timezone-relevant value. When displaying this time value to a user, always convert to the user time zone at that time. (And don't forget both the time and the date depend upon time zone.)

The other concept is that of a "time expression" such as "8:00PM every Tuesday". You specifically mentioned allowing people to schedule appointments, and if you have recurring appointments, you need an expression like that. I don't know of any standard expression language, but whatever you use, it will be relative to the time zone of the person who specified it. It would be best to make this explicit e.g. "8:00PM every Tuesday in Pacific Time Zone". In the case of a time expression, you store this always as a string, and never involve any system time formats.

See more discussion of this on my blog

1

Many answers here indicate storing as UTC. But be really careful with this. For instance, if you schedule an appointment at 12:00 but the appointment takes place after the change to daylight saving time, what will happen? UTC does not keep any information on whether dst was active when the appointment was stored. A lot of big, famous systems have made this error, where a user sends an email at 9am in the summer, and then in the winter the sent time shows 8am, because the calculation back from UTC depends on when you look at the datetime, not on when the datetime was recorded.

Much better is to assume your user wants to have the times he picked always. No UTC conversion, no time conversion, no timezone info, nothing. Making an appointment from 08:00 to 12:00 on the 21st of March 2016 is just that. Don't use either local time or UTC time, but unspecified time (in json this has neither z nor +, basically, in .NET this has DateTime.Kind = DateTimeKind.Unspecified).

Of course, if your use case is that you are a company that holds meetings with someone from different timezones and you want to see this information in, say, a company calendar, but allow users to see what time that is in their timezone, it gets more complicated. The time has to be correct for different people in different timezones (the supplier and the customer).

In those cases, you may even want to record when the appointment was saved to the database, in what timezone and if that included dst or not. That way, you can always calculate back the local time to anything else, either to what it would be now or to what it was intended to be historically. Because timezones are very much not static, making things even more complicated.

Fortunately, this is where libraries like http://nodatime.org/ come in and are highly recommended. They work with dates much more consistently. Even then I would recommend wrapping all your datetime variables and logic in your own wrappers, using interfaces so they can be mocked, and then you can still switch logic later.

  • I don't quite agree with your conclussions, but a meeting at noon scheduled across a daylight savings change is a special challenge, as would be a weekly meeting at noon booked several years into the future. – Bent Sep 10 '16 at 13:52
  • "Of course, if your use case is that you are a company that holds meetings with someone from different timezones and you want to see this information in, say, a company calendar, but allow users to see what time that is in their timezone, it gets more complicated." - that's pretty much every company. Very few companies outside China and India can ignore cross-timezone appointments. – Ross Patterson Oct 2 '16 at 22:05
  • "That way, you can always calculate back the local time to anything else, either to what it would be now or to what it was intended to be historically. Because timezones are very much not static, making things even more complicated." - once you accept that you're going to make calculations, you really need to choose a single representation for time. UTC, EST (but not EST5EDT), IST, whatever. But you simply cannot have values based on multiple timezones in the same field and do any legitimate aggregate processing on them. Display processing, sure. But that's the easy part. – Ross Patterson Oct 2 '16 at 22:08
  • At least you still can, correctly, convert to UTC. You can always create a calculation table or value from this information in retrospect. But you can never go the other way. Note that SQL 2016 starts supporting this natively, though unfortunately it still relies on the - at least historically - not quite correct registry data - but it's yet another improvement. – Arwin Oct 3 '16 at 20:14

protected by gnat Sep 10 '16 at 3:49

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.