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
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?
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.
See more on this question at Stackoverflow