2

I am building a c# calendar application and have stored all the datetimes in Microsoft SQL-Server DateTime2 type. This data type is searchable using operators such as ">",">=" etc..

I have now read more on the subject for example these posts:

Daylight saving time and time zone best practices

How to store repeating dates keeping in mind Daylight Savings Time

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

I believe I have made an error when using UTC and dealing with different DST values and especially when related to future repeated events.

My current implementation works fine for events, until a repeating series goes over a DST time change.

I believe I will now need to store local times, the local timezone and possibly UTC time

How should I structure my database and what data types should I use to store my data in the database that will support different client timezones and DST values whilst also allowing me to query for matches within specified start and stop datetime ranges?

Community
  • 1
  • 1
Dizzle
  • 1,026
  • 13
  • 26
  • 1
    Storing UTC time is surely a good idea, UTC can be converted to any other timezone condidering the daylight saving, the reverse is not always possible. UTC is continuous, other schemes are often not. So storing UTC in the database, doing calculations with UTC and only converting them for displaying is a good way to avoid a lot of problems. – martinstoeckli Oct 13 '15 at 19:59
  • 1
    the problem ive noticed is when viewing my events via fullcalendar in 2 weeks the clocks will change and the future events are not at the correct local time as they are at an exact UTC time irrespective of DST/timezone – Dizzle Oct 13 '15 at 20:03
  • Ok i see, then you really have to store the timezone toghether with the entered datetime, you are right. One should really think about disposing this daylight saving, was never sure what they are saving anyway. – martinstoeckli Oct 13 '15 at 20:08
  • yes i think so do you know how this should be stored for c#,MSSQL applications which will also support searching within datetime ranges? – Dizzle Oct 13 '15 at 20:10
  • how would this work with multiple global timezones? – Dizzle Oct 13 '15 at 20:22
  • @martinstoeckli: The time zone absolutely does matter. Different time zones will change DST on different dates. For repeated events which are attended in multiple time zones, you absolutely *have* to know the time zones involved. – Jon Skeet Oct 13 '15 at 23:43
  • Coming to this late, but agree with Jon 100%. Scheduling future events by the local zone requires you retain the local zone and local time. Otherwise if the relationship between local and UTC changes before your event comes around, you'll fire based on what it *used* to be, rather than what it actually is at that time. – Matt Johnson-Pint Oct 14 '15 at 00:38
  • @martinstoeckli: But *why* would you ignore daylight saving for input and output? It changes the time at which the event occurs in local time for other users. For example, if I have an event which is always at 10am Pacific Time... then for a user who is in the London time zone, the event occurs at 3pm, 4pm or 5pm depending on the exact date. Why would you *not* want to have that information? – Jon Skeet Oct 14 '15 at 12:33
  • @JonSkeet - To avoid a longer discussion i wrote an answer and removed some of my previous comments. – martinstoeckli Oct 14 '15 at 15:14
  • I now realize that I will be able to store all my data in UTC but i must generate the new occurrences of events in the local time and then convert those to UTC before saving. Thanks for the help – Dizzle Oct 17 '15 at 19:47

1 Answers1

5

For repeated events, you definitely need to store the time zone, yes, and I'd store the local date/time. You might also want to store the UTC value of the first occurrence, if that would be useful for comparison purposes. In theory you could just store the UTC occurrence of the first date/time, as that can be unambiguously converted to the local time (if you have the time zone) - but it's likely that you'll only ever need the local time, in which case performing those conversions may be pointless.

You should also consider how you want to handle changes in time zone data - because time zone rules do change, reasonably frequently. (It depends on the country, admittedly.) For example, for efficiency you may want to generate a certain number of occurrences and store the UTC date/time of each occurrence (having worked out what to do with skipped and ambiguous local times due to DST transitions) - but if the time zone data changes, you'll need to perform that generation step again for all repeated events.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Thanks for the input but I was also wondering how to model this in the database. It doesnt seem effecient to have to retrieve a dataset and then parse the utc time and the offset and then query the results to make a time range filter. How would i structure this – Dizzle Oct 13 '15 at 20:37
  • @Dizzle: I don't know what you mean by "search for time zone results". Again, you might want to store UTC values as well as the local values, precisely for searching without conversions, but it's hard to know what the requirements are beyond that. – Jon Skeet Oct 13 '15 at 20:38
  • I just require to search within start stop datetime ranges for multiple timezone users. What i dont understand is if i have one entry with a local time and one entry with a timezone string how can i search this at run time without turning them into a datetime object? – Dizzle Oct 13 '15 at 20:44
  • @Dizzle: Well each user presumably wants to search in their own time zone. So either you need to translate each occurrence of each event into the local time zone and search on local times, or convert the search into UTC (which doesn't always make sense) and search on UTC times. I think keeping local occurrences makes the most sense from a search perspective, personally. (Still as a DateTime, just a local one.) – Jon Skeet Oct 13 '15 at 21:02
  • when talking local time do we mean for the users or the server? and if storing for the users local time how could multiple users in different timezones search the same data set? – Dizzle Oct 13 '15 at 21:07
  • 1
    @Dizzle: User. The server time zone is irrelevant. So if you have multiple users in different time zones you may need several copies of the data, one in each zone. It really depends on the kinds of searches you need to support. – Jon Skeet Oct 13 '15 at 21:16
  • I now realize that I will be able to store all my data in UTC but i must generate the new occurrences of events in the local time and then convert those to UTC before saving. Thanks for the help – Dizzle Oct 17 '15 at 19:48
  • @Dizzle: You'll need the time zone as well - otherwise you won't be able to predict when the next occurrence is. – Jon Skeet Oct 17 '15 at 19:49
  • Yes i now understand that i will need the timezone for the repeat configuration. – Dizzle Oct 18 '15 at 20:01
  • @JonSkeet Great answer. However, could you please elaborate on the reasons and advantages of generating a certain number of occurrences and storing the UTC date/time of each occurrence? Why not just generate one future occurrence at the time? Thank you. – damirstuhec Sep 30 '17 at 18:03
  • @JonSkeet Also, I would love to hear what do you think of this technique for storing recurring future events: https://stackoverflow.com/a/16659802/1677480. – damirstuhec Sep 30 '17 at 18:05
  • @damirstuhec: I don't know what you mean by "one future occurrence at the time" but if you expect that users will look at "the current year and the next year" frequently, but later years only occasionally, then it's more efficient to expand the recurrence once and cache the occurrences for the common case (this year/next year) than it is to expand the occurrence *every* time the user looks at their calendar. – Jon Skeet Sep 30 '17 at 19:09
  • @damirstuhec: As for the database idea - the first idea doesn't work in terms of working out non-date events with time zones, e.g. "Every Monday at 5pm in Europe/London time" as they won't be spaced evenly. The second idea works well to find out the events on a particular day, but it doesn't help in terms of expanding a particular recurrence. Basically, you need different techniques for different use cases. – Jon Skeet Sep 30 '17 at 19:12
  • @JonSkeet My use case are weekly events like this: "every Saturday at 10pm in Europe/London time". Users are only interested when the next event is happening, moreover they can't see the ones after that. – damirstuhec Oct 01 '17 at 08:45
  • @damirstuhec: Then do what's right for your particular use case - but don't assume it'll be right for everyone else. (I'm not going to go into details of exactly what to store etc - if you want more information, you should ask a new question. Comment threads aren't for things like this.) – Jon Skeet Oct 01 '17 at 12:03