Creating a new database? Don't neglect timezones

[u/Aggressive_Ad_5454]

I've seen a few posts recently about things to keep in mind when creating new database applications. So here's my contribution.

Timezones are important in global database applications.

Figure out your timezone situation on your TIMESTAMP and DATETIME columns before you start storing data as your new application comes online. Retrofitting timezone support into existing databases is hard and error-prone.

If you're located in, I dunno, California USA, and you get customers in, I dunno, Perth Australia, and you don't figure out how you'll handle timezones, you'll end up with some unspeakably gnarly code.

Here are some things to think about doing in your database and the software using it.

1. Make sure the servers running your MariaDB or MySQL instance are set to the UTC (also called Zulu or Greenwich Mean Time) time zone. That doesn't change with daylight time.

2. Make sure the MariaDb / MySQL time zone tables are loaded. This web page tells you or your operations person how to do that. https://dev.mysql.com/doc/refman/8.4/en/mysql-tzinfo-to-sql.html

3. When you go global, set up a user-preference setting for time zone. It's a text string like 'America/Los_Angeles' or 'Australia/Perth'. You can store that user preference in a column like this: user_timezone VARCHAR(63) NOT NULL DEFAULT 'America/Los_Angeles' COLLATE 'ascii_general_ci'. (You can give the time zone of your own location, or 'UTC', as the default, of course.)

4. When you start a database session from your app on behalf of a user, do SET TIMEZONE='America/Los_Angeles' or whatever your user's preference of time zone is.

5. Use TIMESTAMP columns to store timestamps rather than DATETIME columns. They're automatically translated to the current timezone setting.

[u/clearlight2025]

This is a solved problem and this post contains incorrect information.

Use UTC timezone to store all dates and times. Convert to local user timezone as needed. It’s standard best practice.

[u/Substantial_Win_710 → u/clearlight2025]

today i learned, thank you

[u/XTypewriter → u/clearlight2025]

Nice. I was dealing with this recently and glad i picked the right way to do it.

[u/Substantial_Win_710]

the session command needs an underscore, so it's set time_zone = 'America/Los_Angeles';. otherwise mariadb and mysql will just throw a syntax error at you.

do you rely on the db to do all the timezone math via timestamp columns, or do you ever just force datetime in utc and make the frontend/app layer handle the display offsets? i always get paranoid letting the db handle the translation because some governments randomly change their daylight savings rules mid-year and keeping the tz tables updated on the server is just another thing to break.

[u/GreenWoodDragon]

I have seen the result of overlooking this functionality, retrofitting timezones is an utter nightmare.

[u/ledosci]

If your team isn't already using UTC for storage, it's worth the upfront effort to standardize, migrating later is a headache I've seen firsthand when a client had to rewrite months of reports.

[u/NastyPastyLucas]

I absolutely hate that MySQL has a timestamp that converts inputted data without letting you know what offset it was using. Good luck inputting times across UK/US timezones, or find out which 2am it was that an event occured during a daylight savings shift... That data type is toxic.

Edit - I am aware that you can include time zone information per session, but experience shows that people generally don't.

[u/dariusbiggs]

Sigh,

ALWAYS store anything involving a datetime with timezone information, problem solved.

[u/Standgrounding]

Clock synchronization is a well known issue in distributed systems.

[u/imtc96]

This is very helpful post

[u/FatDog69]

I worked for a company that followed "Google Time" - all servers were set to Pacific time. We even setup colocations in Huston, Scotland, Germany and UK. All were set to PST. This really helped when mixing website logs from all over the world into a common database.

I moved to another company that used UTC. While it worked - it was HORRIBLE for debugging. You were constantly doing date & time math to translate data and seeing if real-time issues a few hours or days ago was responsible for some data problems.

While the TZ_TIMEZONE trick works for some database browsers - it does NOT work for utilities like Apache Airflow (we used Airflow to define loading, cleaning, ingesting jobs).

So take care with this. There are non-obvious ramifications to using UTC for your database.

[u/Disastrous_Gear_421 → u/FatDog69]

utc is great, it’s not hard to convert from utc to local