TIMESTAMP Data Type - Oracle to MySQL Migration

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

Oracle:

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

MySQL:

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

Conversion summary:

	Oracle	MySQL
Syntax	TIMESTAMP[(p)]	DATETIME[(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

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

However, the largest date it can store is 2038-01-19.

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.

MySQL:

  -- 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 MySQL Migration.