Design timezone support into your MariaDB or MySQL app

by

Timezones are important in global database applications. If you’re setting up a new database and software to use it, it’s smart to think through timezones early in your project.

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.

Leave a Comment