How to Convert Timezones in MariaDB

Galaxy Glossary

How do I convert timezones in MariaDB?

Use CONVERT_TZ() or SET time_zone to shift DATETIME/TIMESTAMP values between zones in MariaDB.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does CONVERT_TZ() do?

CONVERT_TZ(datetime_expr, from_tz, to_tz) shifts a DATETIME or TIMESTAMP from one zone or offset to another. It returns NULL if either zone is unknown or missing from the time-zone tables.

How do I load time-zone tables?

Run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql. Without this data, named zones like 'America/New_York' are unresolved and CONVERT_TZ() fails.

How do I set the session time_zone?

SET time_zone = 'UTC'; changes the conversion context for NOW(), CURRENT_TIMESTAMP, and TIMESTAMP arithmetic for the current connection only.

When should I store UTC?

Store all timestamps in UTC for consistency and index efficiency. Convert to the user’s zone at query time with CONVERT_TZ() or by switching @@session.time_zone.

Example: convert order timestamps to customer zone

SELECT o.id, o.order_date AS utc_time, CONVERT_TZ(o.order_date, '+00:00', c_tz.tz) AS local_time FROM Orders o JOIN Customers c ON c.id = o.customer_id JOIN CustomerTimezones c_tz ON c_tz.customer_id = c.id;

How to convert using numeric offsets?

Pass offsets directly: CONVERT_TZ(order_date, '+00:00', '-05:00'). Useful when the client sends only the offset.

How to detect missing zone data?

If CONVERT_TZ() returns NULL, the zone is missing. Filter with WHERE local_time IS NULL and alert or fallback to UTC.

Best practices for ecommerce apps

• Keep DATETIME/TIMESTAMP columns in UTC.
• Store each user’s IANA zone or numeric offset in a profile table.
• Use CONVERT_TZ() in SELECT or in a VIEW, not during INSERT.
• Index UTC columns for date-range searches to stay sargable.

Why How to Convert Timezones in MariaDB is important

How to Convert Timezones in MariaDB Example Usage


-- Show today’s US-Pacific sales total in UTC and local time
SELECT SUM(total_amount)                                        AS total_usd_utc,
       CONVERT_TZ(SUM(total_amount),'UTC','America/Los_Angeles') AS total_usd_pst
FROM   Orders
WHERE  order_date >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 DAY);

How to Convert Timezones in MariaDB Syntax


CONVERT_TZ(datetime_expr, from_tz, to_tz)
  • datetime_expr  – DATETIME or TIMESTAMP value to convert
  • from_tz        – Source zone name (e.g., 'UTC') or offset ('+00:00')
  • to_tz          – Target zone name ('America/New_York') or offset ('-05:00')

-- Set session zone for current connection
SET time_zone = 'zone_name' | '+/-HH:MM';

-- Ecommerce example: convert order_date (stored UTC) to US Eastern
SELECT CONVERT_TZ(order_date,'UTC','America/New_York') AS eastern_time
FROM   Orders;

Common Mistakes

Using named zones without loading mysql.time_zone tables causes CONVERT_TZ() to return NULL. Fix by loading zoneinfo data with mysql_tzinfo_to_sql.
Storing local times directly leads to daylight-saving ambiguity and hard-to-index ranges. Store UTC in the database and convert on output.