一、Mysql 的 DATE、DATETIME 和 TIMESTAMP 类型区别
-
DATE 类型
该 DATE 类型用于包含日期部分但不包含时间部分的值。MySQL 以格式'YYYY-MM-DD'检索并显示 DATE 值 。支持的范围是 '1000-01-01'到'9999-12-31'。
-
DATETIME 类型
该 DATETIME 类型用于包含日期和时间部分的值。MySQL 以格式 'YYYY-MM-DD hh:mm:ss' 检索并显示 DATETIME 值 。支持的范围是 '1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
-
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 类型区别
-
DATE 类型
虽然名称是“DATE”,但它还存储有关时间的信息。在内部,DATE 将年、月、日、小时、分钟和秒存储为显式值。
-
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 ,这允许我们存储带有时区的日期/时间。
-
DATE 和 TIMESTAMP 比较
具有相同的大小(7 字节)。这些字节用于存储世纪、年、月、日、小时、分钟和秒。但 TIMESTAMP 允许存储附加信息,例如秒小数部分(11 字节)和带时区的秒小数部分(13 字节)。
![alt](https://i-blog.csdnimg.cn/blog_migrate/d735daacf1fa249e822f050f7b949aca.png)
三、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 多平台发布