Introducing user selectable timezones to existing high-traffic web app?

Long story short, we developed a web app for internal use and one thing led to another and now it’s a SaaS with over 1,000 users. Problem is that everything date-related (which is a lot!) is hard-coded to Pacific time and users obviously want to select their own timezones.

I’ve read up on the best way to manage timezones in web apps, but I’m still not sure on the best way forward with our existing, live, mission-critical web app used by over 1,000 businesses…

I guess the big “problem” is just that we’re talking about 100k+ lines of code, and probably at least 100 different DB queries that are date related.

From what I’ve read, it seems like the “correct” way to manage timezones is to store everything in UTC and then convert to the users’ TZ on output …

But at this point would there be anything wrong with simply modifying all the date-related queries and basically wrapping all the dates with MySQL’s convert_tz to convert from the existing PDT timestamps to the user’s timezone?

As far as I can tell if we converted all the date fields in the DB to UTC and changed MySQL to run under UTC we’d have to do the same thing anyway, so is there any particular reason to convert all the existing date fields to UTC at this point?

Another idea I had was to just leave the existing timestamps the way they are for our own internal use/queries/etc, add a new field to the applicable tables to store the same timestamp in the user’s selected timezone (inititally identical to the existing timestamp field until they change the TZ from the current PDT to something else), and just modify all the queries to query the new field with the user selected timezone…

Does it really matter at this point? Just looking for some input on the easiest and/or most efficient way to do this. It’s a relatively small (but needed) feature, but it looks like it’s gonna be a bunch of work no matter what lol

Anyway, thanks for the help if you have any insights!

When you run these queries, are they all using the same API? If so, this may be a lot less painful than you are anticipating.

To clarify, are you using some sort of ORM or database class to process queries, or were each one of those 100+ queries written by hand?

Yeah we are just using Perl’s DBI with MySQL. All the queries were written “by hand.”

What I meant was, do you have a class that defines each query, and you reuse the class to make those queries? From your answer above, I assume you wrote 100+ unique queries, rather than reusing predefined ones, in which case you were almost asking for this situation to happen.

Yeah they are all unique queries, some quite elaborate. Originally this was just whipped together for internal use … then it grew a bit … then it grew a bit more … and now here we are with 1,000 customers using it. We had no idea or plans for this to happen.

Damn. Well, was hoping there would be a relatively quick fix to this, but doesn’t look like there is one.

I’ll brainstorm on this for a minute; hopefully someone has had a similar experience and can chime in meanwhile.

Another idea…

What if we just create a function whereby when the user selects a different timezone, the system would just go through all the relevant tables and change the value of all their existing timestamps from PDT to their selected timezone via MySQL’s convert_tz function? We’d also make a few other small tweaks so that the system would also start logging all new timestamps in their selected timezone going forward.

Making these changes wouldn’t take more than a handful of hours, whereas if we go the other route I estimate ~100+ hours to convert everything to UTC, go through all the code literally line by line, redo 100+ queries, and then test everything thoroughly before we can go live with it.

It may not be the “right” way to go but it seems like it would work. What do you think?

Most likely when the user wanted to change their timezone we’d just spawn another process and do it in the background, and let them know the change will be complete in 5-10 minutes (we’ll have to throttle the updates some as the relevant tables have 10s of millions of rows).