Dev: Timezones & Time travel
Anyone who has to deal with time, specifically timezones, has experienced the utter frustration they can cause.
Initially when someone would submit a survey response we would capture the time in UTC, this is a fairly standard practice in the software development world. This works well if your co-located in the same timezone. For example, if I am in mountain time, and someone took my survey in mountain time, then all is well.
However, if I am in another timezone from where my respondent is then we can experience the phenomenon known as time travel. If the respondent took the survey at 11pm Mountain Time on 5/1/2020, but I am located in Eastern Time the survey magically time travels to 5/2/2020 at 1 am.
This isn’t technically wrong, but it can be confusing to see responses on different days on graphs, etc. And is even more confusing when date calculations are made on the server.
To solve this, we made the following changes:
- Dates are captured by the client, in the local time.
- The date/time and its timezone information are sent to the server and stored.
- Previous dates, that were stored in UTC, were time traveled back to their original time zones.
Voila! Done!
Not quite. There are still some details, we’re about to get technical.
Now that we have the date time information in the timezone of the client, we need to store it right.
Use DateTimeOffset
Generally, we use DateTime in Sql, however this loses the offset (or timezone information) so a date/time of 5/1/2020 11pm mountain time, is stored as just 5/1/2020 11pm, no timezone information.
DateTimeOffset, however stores it as 5/1/2020 11pm -6, letting future calculations know that the time is -6 hours from UTC, or that the time is 5/2/2020 5am in UTC time.
Date Only Comparison
Date and time are technically the same thing, but often times its nice to look at them separately.
Now that we are storing our response as 5/1/2020 11pm -6, if we want to filter to only responses on 5/1/2020 we can use just the date information and get all responses taken on 5/1/2020 relative to their specific timezone, not relative to where I am at.
This means if I am in Easter Time, or Pacific Time, or if I am the server trying to send out a weekly email, the same information is displayed irregardless of where I am at.
Other Thoughts
As with all things timezone, it depends on what you want. If I have a survey, with respondents across the world, a response taken on 5/1 in say Nepal, would also show up on 5/1.
This could be shown differently, we would just need to convert time back to UTC (which most systems can do easily) or do a date/time comparison which will take into account the timezone information (on most systems)
