I have a table that stores events from all around the world. The table has around a million rows and is queried several times a second 24 hours a day. In most cases the event is recorded in UTC but there are about 10% of the records that are in their local time zone. A column in the table identifies when the datetime is UTC and when it is local to the event's location.
I think the best way to handle the 2 types of datetimes might be to unify everything to use UTC and then base my queries on that. That would allow me to take advantage of a meaningful index on the unified column. But the downside is how do I then handle DST? Should I set a job up to run every so often to update the unified column's value and account for DST or is there a better way to handle this? I'm trying to avoid calculating a UTC value every time the query is called since it is called so frequently and the local time would only change twice a year (hopefully).
Any suggestions? Does anybody else have a similar situation that they have a solution for?