A an explanation of MySQL timezones with MySQL client examples showing how MySQL stores and displays date/time values, how UTC/GMT fits into this, and how client time zone settings affect what you see.

Understanding How MySQL Stores and Displays Date/Time Values

Working with date and time in MySQL can be confusing, especially when you deal with multiple time zones. This article explains:

1. How MySQL internally stores TIMESTAMP and DATETIME values.
2. How MySQL’s time zone settings affect storage and display.
3. Practical MySQL client examples to demonstrate the behavior.

1. Storage Rules in MySQL

MySQL has multiple temporal data types (DATE, TIME, DATETIME, TIMESTAMP), but the two most commonly compared are DATETIME and TIMESTAMP:

• DATETIME

  • Stored as a literal value (YYYY-MM-DD HH:MM:SS).
  • No time zone conversion takes place.
  • Example: 2025-08-18 15:00:00 is stored exactly as written.

• TIMESTAMP

  • Stored internally in UTC (GMT).
  • When a client inserts a value, MySQL converts it from the client’s time zone to UTC for storage.
  • When a client retrieves a value, MySQL converts from UTC to the client’s time zone for display.

2. Checking the Current Time Zone

You can check the server and session time zones like this:

-- Server-wide timezone
SELECT @@global.time_zone;

-- Current session timezone
SELECT @@session.time_zone;

-- What does MySQL think "now" is?
SELECT NOW(), UTC_TIMESTAMP();

Example output (server running in UTC, client in Europe/Warsaw):

+-------------------+
| @@session.time_zone|
+-------------------+
| SYSTEM            |   <-- means it uses OS timezone (Europe/Warsaw)
+-------------------+

+---------------------+---------------------+
| NOW()               | UTC_TIMESTAMP()     |
+---------------------+---------------------+
| 2025-08-18 16:00:00 | 2025-08-18 14:00:00 |
+---------------------+---------------------+

3. Inserting Data

Let’s create a simple table:

CREATE TABLE tz_test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ts TIMESTAMP,
  dt DATETIME
);

Insert one row with the same literal value:

INSERT INTO tz_test (ts, dt)
VALUES ('2025-08-18 16:00:00', '2025-08-18 16:00:00');

4. Retrieving Data

If the client session is set to Europe/Warsaw (UTC+2):

SELECT ts, dt FROM tz_test;

Output:

+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2025-08-18 16:00:00 | 2025-08-18 16:00:00 |
+---------------------+---------------------+

Now switch the session timezone to UTC:

SET time_zone = '+00:00';

SELECT ts, dt FROM tz_test;

Output:

+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2025-08-18 14:00:00 | 2025-08-18 16:00:00 |
+---------------------+---------------------+

👉 Notice the difference:

• TIMESTAMP shifted by 2 hours (it was stored as UTC, then adjusted for display).
• DATETIME remained unchanged (stored literally).

5. How This Affects Applications

• If you need to track exact moments in time globally (e.g., logins, events, transactions), use TIMESTAMP. It’s time zone–aware and always stored in UTC.
• If you need to store calendar-like events (birthdays, business hours) where the actual wall-clock time matters, use DATETIME.

6. Best Practices

1. Always set explicit time zones in your application connection:

   SET time_zone = '+00:00';   -- force UTC

   This avoids surprises when the server or client runs in a different zone.

2. Use UTC_TIMESTAMP() for audit/logging fields.

3. If mixing multiple time zones (e.g., users worldwide), store in TIMESTAMP and convert at the application level for display.

7. Setting the Time Zone on the Client Side

When you connect to MySQL, your session time zone (@@session.time_zone) determines how TIMESTAMP values are interpreted and displayed. Each client or driver has its own way of controlling this.

Here’s how to set it in common environments:

7.1 Bash / mysql CLI

You can set the time zone in your shell before connecting, or directly in SQL after connecting.

# Set environment variable for mysql client
export TZ=UTC
mysql -u user -p

Inside MySQL, you can override:

SET time_zone = '+00:00';       -- UTC
SET time_zone = 'Europe/Warsaw';

7.2 PHP (mysqli / PDO)

Option 1 – Run a query after connection:

$mysqli = new mysqli("localhost", "user", "pass", "db");
$mysqli->query("SET time_zone = '+00:00'");

Option 2 – Use PHP’s default timezone (affects NOW() if passed from PHP):

date_default_timezone_set("Europe/Warsaw");

⚠️ This doesn’t automatically change MySQL’s session time zone — you must still SET time_zone if you want MySQL conversions to match PHP’s.

7.3 Python (mysql-connector / PyMySQL)

import mysql.connector

cnx = mysql.connector.connect(
    user='user', password='pass', database='db',
    option_files='my.cnf'
)

cursor = cnx.cursor()
cursor.execute("SET time_zone = '+00:00'")

For SQLAlchemy or PyMySQL you can also run the same SET time_zone right after engine.connect().

7.4 Node.js (mysql2 / mysql driver)

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'pass',
  database: 'db',
  timezone: 'Z' // 'Z' = UTC, or e.g. '+02:00'
});

The timezone option tells the driver how to interpret TIMESTAMP values when sending/receiving.

7.5 Java / JSP (JDBC)

With the MySQL Connector/J driver, you can set timezone in the JDBC URL:

String url = "jdbc:mysql://localhost:3306/db"
    + "?user=user&password=pass"
    + "&serverTimezone=UTC";
Connection conn = DriverManager.getConnection(url);

• serverTimezone=UTC ensures the driver interprets values correctly.
• If you want to change MySQL’s session time zone explicitly:

Statement stmt = conn.createStatement();
stmt.execute("SET time_zone = '+00:00'");

7.6 Perl (DBI / DBD::mysql)

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=db;host=localhost",
                       "user", "pass",
                       { RaiseError => 1 });

# Force session to UTC
$dbh->do("SET time_zone = '+00:00'");

8. Final Takeaways

• DATETIME = stored and retrieved literally, no time zone conversion.
• TIMESTAMP = stored in UTC, converted to client/session time zone on insert/retrieval.
• What you see depends on session time zone (@@session.time_zone).
• The session time zone is the key factor controlling how TIMESTAMP values are displayed.
• Always set the timezone explicitly when your app connects to MySQL.
• Keep PHP/Python/Java application time zones in sync with your MySQL session time zone to avoid subtle bugs.