[2024-05] Visit dates are now time zone independent

This article describes changes introduced in May 2024 to how we interpret the visit_date column on the Visits data element. It was last updated on May 1, 2024.

Summary

We’ve simplified how Relevant handles time zones for the Visits data element. Now, all screens in Relevant assume that visit_date stores the date and time of the visit in the local time zone of the clinic, and displays it without making any time zone modifications.

Additional details

In the past, many screens in Relevant assumed that visit_date was stored in UTC, and then converted its value to Eastern Time. This caused surprising behavior for a handful of visits, particularly those which occurred in the early morning hours of January 1st.

Now, we assume that visit_date stores the date and time of a visit in the local time zone of the clinic. Rather than attempt any timezone conversions, we have modified many screens in Relevant—including Data Explorer, Visit Calendar, the UDS module, and the UDS+ module—to display this date and time in its original form.

The result is a simpler experience that matches how most users have expected visit dates to behave. It has also allowed us to simplify the SQL which backs the UDS and UDS+ modules. For technical users, you’ll notice that this SQL no longer contains offsets like the following: WHERE visit_date BETWEEN '2023-01-01 05:00:00.000000 AND '2024-01-01 04:59:59.999999'. Such offsets are no longer necessary; if you’re using them elsewhere in SQL for reports or measures, they can also be removed.