Convert local time of day to UTC time

Question

Our ASP.NET MVC web application has a feature to send out regular notification emails to users. We are going to add a feature where users can select a specific time of day to receive their email (i.e. "I would like to receive an email once per day at 5pm").

I plan on storing the selected time using the SQL time type. We'll have a background process which runs every minute and looks for users needing to be emailed. The process is using a LINQ-to-Entities query to find all matching users. I was going to write a query to basically find all users whose daily email time is less than the current UTC time of day - which is where I run into the problem. Each user can belong to a different timezone - we store their timezone as an IANA timezone identifier in the database. We use NodaTime in our project to convert any dates stored in our database as UTC into the user's timezone. To proceed with the way I'm trying to do this, I need something that I can put within a LINQ-to-entities query, so I can't really use any NodaTime functions to convert times and figure out if the current time of day is after the user's specified time of day, in their timezone. I can't just store their specified time of day in UTC time, if I did then it would be off by an hour whenever DST comes around.

The best solution I can come up with right now is to "cache" the current UTC offset for each timezone in our database. So I could have some code that uses NodaTime to grab the current offset for each timezone and saves that to a table in the database that stores the TimeZoneID and the UTCHourOffset. I could have a regular process to run this code and make sure the cached hour offsets stay up-to-date. Then in my query I can convert the specified time of day for each user by looking up the cached UTC offset and adding that to their time of day, to figure out if they should be sent an email.

Is there a simpler way to do what I'm trying to do?

Answer 1

Glad to see you think through this carefully. Indeed, you cannot just store the UTC time when you are trying to schedule a recurring event in a user-local time zone. (Don't let anyone tell you "always UTC" - that's a common misunderstanding, and you indeed have a valid case for local time.) I've written about this a few times, and the most thorough answer is here.

Rather than cache the offset, you should consider pre-calculating the next run time as an exact UTC timestamp (both date and time). Then you can query against that to know when to send the email. Alternatively, consider using a timezone-aware job scheduler that already handles this. Quartz.NET is a good one.

You're correct that you cannot currently use Noda Time types with EF. This is due to a long lacking feature of EF tracked in this issue. The good news is the dev work was recently completed for EF Core 2.1 (still in development). After that releases, someone (probably me) will have to apply that feature to create an EF-NodaTime support package. In the meantime, treat SQL time types as a .Net TimeSpan, and use the Buddy Properties approach to expose the NodaTime types. You'll have to write your LINQ queries against the TimeSpan. You can refactor this later if you move to EF Core 2.1.