TIMESTAMP Data Type - Oracle to MariaDB Migration

In Oracle, the TIMESTAMP(p) data type stores date and time with fractional seconds but without time zone. In MariaDB Oracle compatibility mode, you can also use the TIMESTAMP(p) data type.

Oracle:

  -- Sample table with TIMESTAMP with milliseconds accuracy
  CREATE TABLE specs
  (
    item VARCHAR2(30),
    created_dt TIMESTAMP(3)
  );

MariaDB - Oracle Compatibility:

   -- Sample table with TIMESTAMP with milliseconds accuracy
  CREATE TABLE specs
  (
    item VARCHAR(30),
    created_dt TIMESTAMP(3)
  );

Conversion summary:

	Oracle	MariaDB - Oracle Compatibility
Syntax	TIMESTAMP[(p)]	TIMESTAMP[(p)]
Range	0 ⇐ p ⇐ 9	0 ⇐ p ⇐ 6
Date range	4712 BC to 9999-12-31	1000-01-01 to 9999-12-31
Accuracy	1 nanosecond	1 microsecond
Default	p is 6	p is 0

MariaDB also has the TIMESTAMP data type, which supports fractional seconds.

However, the largest date it can store is 2038-01-19 (before MariaDB 11.5) and 2106-02-07 (MariaDB 11.5 or later).

Additionally, TIMESTAMP converts values from the session time zone to UTC for storage and back to the session time zone for retrieval. This does not occur for DATETIME.

MariaDB - Oracle Compatibility:

  -- Sample table with both data types
  CREATE TABLE ts1 (c1 TIMESTAMP, c2 DATETIME);
 
  -- Change the time zone
  SET time_zone = '-03:00';
 
  -- Insert a sample row
  INSERT INTO ts1 VALUES ('2024-02-20 13:07:00', '2024-02-20 13:07:00');
 
  -- Change the time zone again
  SET time_zone = '+01:00';
 
  SELECT * FROM ts1;
  /*  2024-02-20 17:07:00 | 2024-02-20 13:07:00 */

You can see that the output for the TIMESTAMP column changed. The value was first converted from the '-03:00' time zone to UTC and then from UTC to the '+01:00' time zone. Meanwhile, the DATETIME value remained unchanged.

For more information, see Oracle to MariaDB Migration - Oracle Compatibility.