Drawbacks of setting MySQL @@session.time_zone per query instead of CONVERT_TZ?

We are storing MySQL timestamp columns in our database.

We are serving multiple web customers in multiple timezones, we often have to query the data sensitive to their timezone to allow them to use their local times.

Here is an example of an average query in our system:

SELECT * FROM user
WHERE CONVERT_TZ(created_at, @@session.time_zone, 'America/Denver')
BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59' <--- These are local denver times provided from the customer so we have to convert from the server timezone.

As you can see, our current solution is to leverage the CONVERT_TZ function to convert from the MySQL server timezone interpretation of the timestamp column into the client's local timezone.

This repetition becomes burdensome on developers and can accidentally be left out of crucial queries. We can often have five or six or more instances of CONVERT_TZ in a single query.

To solve this problem we are thinking about leveraging the fact that timestamps are converted automatically by MySQL to the connection time_zone variable as outlined here: https://dev.mysql.com/doc/refman/8.0/en/datetime.html#:~:text=MySQL%20converts%20TIMESTAMP,Zone%20Support%E2%80%9D.

In theory, setting the MySQL time_zone to our customer's timezone prior to the query would automatically make all of their queries able to their local timezone without additional conversions.

This idea yields the following query:

SET @@session.time_zone = 'America/Denver';
SELECT * FROM user
WHERE created_at <-- No longer have to convert the column, because the MySQL time_zone takes affect.
BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
SET @@session.time_zone = 'SYSTEM'; <-- Revert back to system.

Are there drawbacks to this approach? Can multiple queries across sessions/connections be impacted by the variables of another? Are there race conditions between multiple queries that might inherit the incorrect variable before completing?

Session variables never "leak" into other sessions. Session variables cannot change while the query is running.

The biggest drawback is that you still have datetime values stored in different timezones. You can change the session timezone so you can search based on a given user's preference, but there are many cases when this makes things hard. Here are a few examples, but there may be many others:

• Check which of two users was created first
• Sort a set of rows by datetime
• Allow one user to query the datetime of another user
• Check if multiple users can attend the same meeting

And so on. If you store each user's datetimes according the respective user's preference, then you still need to convert one or the other if you want to compare datetimes.

Another downside of storing datetimes in a user's preferred timezone is what if that user moves to another timezone, and they change their preference? You'd have to update all rows that contain times referenced by that user.

The preferred solution is: store all datetimes in UTC.

(Actually, it doesn't matter which TZ you choose, as long as you are consistent. UTC is just a good neutral choice.)

Then you can compare datetimes, you can sort them, and you don't have to worry about developers forgetting how to convert them properly. Convert the values to UTC on insert, and convert them to the user's current preference when you display query results.