MariaDB Tutorial / Store Dates and Times with DATE, DATETIME, and TIMESTAMP

DATE stores a date, DATETIME stores calendar date-and-time fields, and TIMESTAMP represents a time that is converted according to the connection’s time zone. Decide whether you need a local wall-clock value or an event’s instant before choosing a type; the same input can produce different TIMESTAMP displays in different sessions.

Distinguish a date, wall-clock time, and an instant

Coordinated Universal Time (UTC) is a shared reference for comparing instants. Korea’s +09:00 offset means that 09:00 in Korea and 00:00 UTC on that date describe the same instant. Adding the letters UTC to a string does not configure a database connection. Use Calculate Date Intervals, Differences, and Display Formats for calculations and period boundaries, and Inspect Server Settings, SQL Modes, Time Zones, and Logs to compare per-connection time zones with persistent settings.

A DATETIME wall-clock value does not by itself preserve a region’s time-zone or daylight-saving rules. An application may normalize values to UTC or store local fields together with a zone identifier, depending on its purpose. TIMESTAMP also does not retain the name of the original input time zone.

Check the server version and connection time zone

Connect as course_user to the practice server prepared in the installation guides, with the shopcourse database available. The command-line client and SQL-file guide explains the connection and SOURCE command. Run these checks inside that connection, not in the operating-system shell:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

The selected database should be shopcourse. Save a complete normal example as a UTF-8 .sql file and run it with SOURCE and its actual local path. Keep the intentionally failing statements separate: an error can stop a source file. Use a learning instance, not a connection containing production data.

SELECT @@SESSION.time_zone AS session_zone,
       @@GLOBAL.time_zone AS global_zone,
       @@system_time_zone AS system_zone;

SESSION is the current connection; the global setting supplies a default for new connections. SYSTEM is interpreted using the server’s operating-system time zone, so do not infer the actual connection setting from the machine’s location. This exercise uses explicit +09:00 and +00:00 offsets and therefore does not need named time-zone tables.

DATE and DATETIME support years from 1000 through 9999, while TIMESTAMP’s upper limit depends on the server version. Before MariaDB 11.5, its ordinary range ends at 2038-01-19 03:14:07 UTC; from 11.5, the upper limit is 2106-02-07 06:28:15 UTC. The lower bound is 1970-01-01 00:00:01 UTC, separate from special zero values. Check your actual SELECT VERSION() against the version-specific TIMESTAMP limits. Distribution and vendor packages may differ.

Insert once and change only the session time zone

The complete time_lab example inserts the same calendar fields into DATETIME and TIMESTAMP, then changes one setting: the session time zone. It saves both session settings first. Explicit TIMESTAMP NULL DEFAULT NULL avoids relying on implicit first-TIMESTAMP defaults that differ by version and configuration; the table-definition guide explains why inspecting actual definitions matters.

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET @saved_time_zone = @@SESSION.time_zone;
SET SESSION time_zone = '+09:00';
CREATE TABLE time_lab (
  item_id INT PRIMARY KEY,
  event_date DATE NOT NULL,
  wall_time DATETIME NOT NULL,
  instant_time TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO time_lab VALUES
  (1, '2026-10-13', '2026-10-13 09:00:00', '2026-10-13 09:00:00');
SELECT item_id, event_date, wall_time, instant_time FROM time_lab;
SET SESSION time_zone = '+00:00';
SELECT item_id, event_date, wall_time, instant_time FROM time_lab;
SET SESSION time_zone = '+09:00';
SELECT item_id, wall_time, instant_time FROM time_lab;

The TIMESTAMP column has not become two different events. The same instant is displayed for two connection offsets. DATETIME retains its calendar fields. Check the DATETIME definition and TIMESTAMP conversion rules when deciding which behavior an application needs.

Specify automatic values and fractional seconds deliberately

Use DEFAULT CURRENT_TIMESTAMP explicitly when a column should receive a creation time automatically. ON UPDATE CURRENT_TIMESTAMP is a separate choice for automatic changes on updates. A last-modified column does not automatically represent the time an external event actually occurred.

DATETIME(6) and TIMESTAMP(6) specify up to six fractional-second digits. The 6 describes precision below the second, not the year range or total string length. Match column and default-expression precision deliberately, and do not assume every record needs microseconds.

Use unambiguous formats such as YYYY-MM-DD and YYYY-MM-DD HH:MM:SS. Passing locale-dependent strings such as 10/11/26, or storing dates as arbitrary strings, makes conversion and comparison harder to reason about. The DATE reference gives the date type’s format and range.

Diagnose invalid dates and unexpected offsets

After the normal file, run this intentional failure separately in the same strict-mode connection. February 30 does not exist:

INSERT INTO time_lab VALUES (2, '2026-02-30', '2026-10-13 09:00:00', NULL);

Expect an Incorrect date value error, with one row still in time_lab. SQL modes affect invalid-date and zero-value handling; changing the mode merely to bypass an error can change the data’s meaning. Review the SQL mode documentation for the conditions.

If a query is unexpectedly nine hours different, inspect the column type and @@SESSION.time_zone first. Before applying an UPDATE that adds or subtracts nine hours, check whether the application and connection are converting the value twice.

A fixed offset cannot represent an entire region’s daylight-saving history. Named-zone handling needs suitable server time-zone data and an application policy. This exercise deliberately demonstrates fixed-offset conversion only.

Restore the original connection settings

SET SESSION time_zone = @saved_time_zone;
SET SESSION sql_mode = @saved_sql_mode;
SELECT @@SESSION.time_zone, @@SESSION.sql_mode;

Restore these session settings in the same connection that saved the variables. If you disconnected, those variables no longer contain the original values; inspect the new connection instead of applying an empty saved variable.

Before repeating the exercise, use SELECT DATABASE(); and SHOW CREATE TABLE table_name;, substituting each actual table name, to confirm that the targets are the disposable tables created here. Back up any data you need to keep. Only after verifying those targets, remove them with:

DROP TABLE shopcourse.time_lab;

This removes both the named tables and their rows. A data-definition statement such as DROP TABLE is not undone by an ordinary ROLLBACK. You can then rerun the complete normal example. The existing products table from the first product-table exercise is deliberately outside this reset.