Setting Local Time and DST Automation in MariaDB (Linux)

  1. Load Time Zone Tables into MariaDB

By default, MariaDB installations do not contain time zone names. You must import the time zone data from your Linux OS into the MariaDB system tables.

Run this command in your Terminal (not the SQL console): Bash

mariadb-tzinfo-to-sql /usr/share/zoneinfo | mariadb -u root -p mysql

What this does: It reads the Linux zoneinfo files and populates the mysql.time_zone tables.

Password: Enter your MariaDB root user password when prompted.
  1. Test the Time Zone Configuration

Log into your MariaDB console (or use a client like HeidiSQL/phpMyAdmin) and verify that the database recognizes the named zone: SQL

-- Set the zone for the current session SET time_zone = 'Europe/Sofia';

-- Verify if NOW() returns the correct local Bulgarian time SELECT NOW(), @@session.time_zone;

If the time is correct, the import was successful.

  1. Make the Setting Permanent (Global Config)

To ensure the setting persists after a server reboot, you must add it to the MariaDB configuration file.

Open the configuration file (common paths are /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/my.cnf):
Bash

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find the [mysqld] or [mariadb] section and add the following line:
Ini, TOML

default-time-zone = 'Europe/Sofia'

Save and exit (Ctrl+O, Enter, then Ctrl+X).

Restart the service for changes to take effect:
Bash

sudo systemctl restart mariadb
  1. Table Best Practices (DATETIME vs TIMESTAMP)

To ensure your columns behave predictably:

DATETIME: Stores the date and time "as is." Best for created_at if you want a fixed record of the local time.

TIMESTAMP: Converts the time to UTC for storage and back to local time for retrieval. This can be confusing if session zones are not managed correctly.