Mysql/Oracle的DATE、DATETIME 和 TIMESTAMP 类型区别

一、Mysql 的 DATE、DATETIME 和 TIMESTAMP 类型区别

1. DATE 类型

该 DATE 类型用于包含日期部分但不包含时间部分的值。MySQL 以格式'YYYY-MM-DD'检索并显示 DATE 值 。支持的范围是 '1000-01-01'到'9999-12-31'。

2. DATETIME 类型

该 DATETIME 类型用于包含日期和时间部分的值。MySQL 以格式 'YYYY-MM-DD hh:mm:ss' 检索并显示 DATETIME 值 。支持的范围是 '1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

3. TIMESTAMP 类型

该 TIMESTAMP 数据类型用于包含日期和时间部分的值。 TIMESTAMP 范围为'1970-01-01 00:00:01'UTC 到'2038-01-19 03:14:07'UTC。

DATETIME 或 TIMESTAMP 值可以包含尾数小数秒部分，精度高达微秒（6 位数字），插入到 DATETIME 或 TIMESTAMP 列中的值中的任何小数部分都会被存储而不是被丢弃。包含小数部分后，这些值的格式为'YYYY-MM-DD hh:mm:ss[.fraction]'，值的范围为 DATETIME'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.499999' ，值的范围为 TIMESTAMP'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.499999'。小数部分应始终与其余时间用小数点分隔；不识别其他小数秒分隔符。

从 MySQL 8.0.19 开始，插入的日期时间值也支持时区偏移；MySQL 将TIMESTAMP值从当前时区转换为 UTC 进行存储，并从 UTC 转换回当前时区进行检索。（对于其他类型，例如 ，不会发生这种情况DATETIME。）默认情况下，每个连接的当前时区是服务器的时间。时区可以针对每个连接进行设置。只要时区设置保持不变，您就会得到与存储的相同的值。如果您存储一个TIMESTAMP值，然后更改时区并检索该值，则检索到的值与您存储的值不同。出现这种情况是因为两个方向的转换未使用相同的时区。当前时区可用作time_zone系统变量的值。

从 MySQL 8.0.19 开始，您可以在向表中插入值TIMESTAMP时 指定时区偏移量。DATETIME偏移量附加到日期时间文字的时间部分，没有内部空格，并使用与设置系统time_zone变量相同的格式，但有以下例外：

• 对于小于 10 的小时值，需要有前导零。

• 该值'-00:00'被拒绝。

• 不能使用 、 'EET'等 时区名称；也不能在这种情况下使用。 'Asia/Shanghai''SYSTEM'

插入的值的月份部分、日期部分或两者都不能为零。从 MySQL 8.0.22 开始强制执行此操作，无论服务器 SQL 模式设置如何。

此示例说明使用不同的设置将带有时区偏移的日期时间值插入到TIMESTAMP和 DATETIME列 中time_zone，然后检索它们：

mysql> CREATE TABLE ts (
    ->     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col TIMESTAMP NOT NULL
    -> ) AUTO_INCREMENT = 1;

mysql> CREATE TABLE dt (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col DATETIME NOT NULL
    -> ) AUTO_INCREMENT = 1;

mysql> SET @@time_zone = 'SYSTEM';

mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = '+00:00';

mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = 'SYSTEM';

mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = '+00:00';

mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = 'SYSTEM';

mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EST                |
+--------------------+

mysql> SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 |          1577891410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
| 2020-01-01 10:10:10 |          1577891410 |
| 2020-01-01 04:40:10 |          1577871610 |
| 2020-01-01 18:10:10 |          1577920210 |
+---------------------+---------------------+

mysql> SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 |          1577891410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
| 2020-01-01 05:10:10 |          1577873410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
+---------------------+---------------------+

选择日期时间值时不会显示偏移量，即使在插入日期时间值时使用了偏移量。

支持的偏移值的范围是 -13:59到+14:00，包含在内。

二、Oracle 的 DATE 和 TIMESTAMP 类型区别

1. DATE 类型

虽然名称是“DATE”，但它还存储有关时间的信息。在内部，DATE 将年、月、日、小时、分钟和秒存储为显式值。

2. TIMESTAMP 类型

Oracle TIMESTAMP 是一种以世纪、年、月、日、时、分、秒的格式存储的数据类型。TIMESTAMP 是 DATE 数据类型的扩展。TIMESTAMP 数据类型是 DATE 数据类型的增强.

• 它允许以秒为单位存储日期时间。
• 默认情况下，TIMESTAMP 的格式为“YYYY-MM-DD HH24:MI:SS.FF”。
• 默认情况下，带有时区的 TIMESTAMP 格式为“YYYY-MM-DD HH24: MI: SS.FF TZH: TZM”。这里 TZH 是时区小时，TZM 是时区分钟。
• 秒分数的位数/长度可以指定为 0 – 9 位。默认长度为 6 位。
• Oracle 还引入了 TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE ，这允许我们存储带有时区的日期/时间。

3. DATE 和 TIMESTAMP 比较

具有相同的大小（7 字节）。这些字节用于存储世纪、年、月、日、小时、分钟和秒。但 TIMESTAMP 允许存储附加信息，例如秒小数部分（11 字节）和带时区的秒小数部分（13 字节）。

三、mysql 和 oracle 时间类型映射

Oracle	说明	MySQL
DATE	Date and time	DATETIME
TIMESTAMP(p)	Date and time with fraction	DATETIME(p)
TIMESTAMP(p) WITH TIME ZONE	Date and time with fraction and time zone	DATETIME(p)

Oracle 提供了 TIMESTAMP WITH TIME ZONE 数据类型，允许您存储带有时区信息的日期时间。

MySQL中没有 可以存储时区信息的数据类型。

当您将带有时区信息的现有数据从Oracle传输到MySQL时，您可以使用Oracle中的SYS_EXTRACT_UTC函数来获取UTC时区的所有时间戳：

根据应用程序，您可以将时区信息存储在同一表的单独列中，或存储在单独的表中。

在我们的示例中，有一个存储机场及其时区的表是有意义的，如下所示：

  -- Airport time zone information
  CREATE TABLE airport_timezone
  (
     airport CHAR(3),
     timezone VARCHAR(30)
  );
  
  
  INSERT INTO airport_timezone VALUES ('SFO', '-08:00');
  INSERT INTO airport_timezone VALUES ('LHR', '+00:00');

现在，当用户搜索机场的航班时，您可以使用 MySQL 中的 CONVERT_TZ 函数将 UTC 时区转换为指定机场的时区：

  -- Show departure times for San Francisco International Airport
  SELECT d.airport, airline, flight, CONVERT_TZ(departure, '+00:00', timezone)  
  FROM departures d, airport_timezone tz 
  WHERE d.airport = 'SFO' AND d.airport = tz.airport;

Airport	Airline Flight	Departure
SFO	Virgin Atlantic VS020	2013-03-21 17:25:00

本文由 mdnice 多平台发布