兼容MySQL时间类型

相比于原始的openGauss,dolphin对于日期/时间类型的修改主要为:

  1. 修改date/time/datetime/timestamp类型的表现。
  2. 新增year数据类型。

注意:由于 openGauss 固有的特性导致无法完全兼容 MySQL 时间数据类型的所有特性,因此需要根据本文档的要求进行特性的使用,避免使用文档描述外的特性,同时兼容后的特性已经覆盖了绝大部分场合的使用需求。

下列表格为兼容 MySQL 数据库后时间数据类型后的基本属性

类型描述存储空间取值范围(用户可输入范围)精度范围备注
date日期4 字节4713 BC ~ 5874897 AD-(1)输入必须为有效日期,不支持月份或者天数为零值;(2)若年份大于等于10000,必须以'YYYY-MM-DD'的形式进行输入;(3)若输入数据没有指定是BC还是AD,则默认为AD;(4)MySQL date类型的范围限制为‘1000-01-01’到‘9999-12-31’,openGauss的范围限制为‘4714-01-01 BC’到‘5874897-12-31’;
time(p)可以用于表示一天中的时间或者一段时间(时分秒),p 表示精度8 字节-838:59:59[.frac] ~ 838:59:59[.frac]p表示小数点后的精度,取值范围为0~6,如不指定默认为 0-
datetime(p)日期和时间,不带时区信息,p 表示精度8字节4713 BC ~ 294276 ADp表示小数点后的精度,取值范围为0~6,如不指定默认为 0(1)输入必须为有效日期,不支持月份或者天数为零值;(2)当输入的年份大于等于10000时,必须使用'YYYY-MM-DD'的格式进行输入;(3)MySQL datetime类型的范围限制为'1000-01-01 00:00:00' 到'9999-12-31 23:59:59',openGauss的范围限制为‘4713-01-01 00:00:00 BC’到‘294276-12-31 23:59:59’。
timestamp(p)日期和时间,带时区信息,p 表示精度8字节4713 BC ~ 294276 ADp表示小数点后的精度,取值范围为0~6,如不指定默认为 0(1)输入必须为有效日期,不支持月份或者天数为零值;(2)注意,timestamp 类型在原来 openGauss 数据库中表示不带时区的时间戳,兼容后的 timestamp 类型往 MySQL 数据库靠拢,表示带时区信息的时间戳,因此存在兼容性问题;(3)当输入的年份大于等于10000时,必须使用'YYYY-MM-DD'的格式进行输入;(4) MySQL timestamp类型的范围为'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC,openGauss的范围限制为‘4713-01-01 00:00:00 BC’ UTC到‘294276-12-31 23:59:59’ UTC。
year(w)年份,w 表示 “display width”,year(4)、year 形式输出为 'YYYY' 形式,year(2) 形式输出为 'YY'2字节1901 ~ 2155--

备注

  • 注意,对于 MySQL,在使用 CREATE TABLE 或者 ALTER TABLE 语句中,如果定义时间类型(例如 timestamp、datetime、time)列属性时不指定精度,则默认为 0。同时,使用 cast(expr as typename) 语法进行类型转换时,如果目标类型没有指定精度,那么默认精度也为 0。因此,如果用户需要保留数据的输入精度,则需要显式使用 typmod。
  • 同时,兼容后的时间类型,使用 :: 进行类型转换,如果目标类型没有指定精度,那么默认精度也为 0。

date 类型输入

支持如下格式:

格式含义
'YYYY-MM-DD' , 'YY-MM-DD'年月日
'YYYYMMDD' 、'YYMMDD'年月日
YYYYMMDD、YYMMDD年月日

备注:

  • 输入必须为有效日期,不支持月份或者日期为零值,月份或者日期出现为0的时候,表现与mysql在sql_mode中打开NO_ZERO_IN_DATE行为一致,具体表现如下所示:

    openGuass dolphin.sql_mode配置值所等价的mysql sql_mode配置值
    dolphin.sql_mode=’sql_mode_strict,no_zero_date‘sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE‘
    dolphin.sql_mode=’sql_mode_strict‘sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE‘
    dolphin.sql_mode=’no_zero_date‘sql_mode=’NO_ZERO_IN_DATE,NO_ZERO_DATE‘
    dolphin.sql_mode=’‘sql_mode=’NO_ZERO_IN_DATE‘
  • 由于 MySQL 原本的年份取值范围在 10000 以内,因此由于 MySQL 原本的年份取值范围在 10000 以内,因此如果想要输入大于等于 10000 年份的日期,请使用 'YYYY-MM-DD' 这种格式,例如 '10100-12-12'

  • openGuass关闭dolphin.sql_mode的no_zero_date时允许输入 0000 年,同时在 openGauss 中,认为 0000 年为闰年,可以输入 0000-2-29 (MySQL 不允许)

  • 对于输入值为非法数据场景,dolphin.b_compatibility_mode开启时,显式转换(如select cast('2022-05-05 20:70' as date))和function转换(如select date('2022-15-05'))场景下返回NULL。dolphin.b_compatibility_mode关闭时则可能会对非法的时间数据按照正常的数据解释处理(如分秒数据超出范围时按往前进位处理等场景)、强制返回0(如insert ignore等场景)处理或者直接提示错误处理(如格式不对等场景)。

示例(注意下方 openGauss 数据库兼容性为 b)

--创建表。
openGauss=# CREATE TABLE test_date(
openGauss(# dt date);
CREATE TABLE
    
--插入数据。
openGauss=# INSERT INTO test_date VALUES ('2020-12-21');
INSERT 0 1
openGauss=# INSERT INTO test_date VALUES ('141221');
INSERT 0 1
openGauss=# INSERT INTO test_date VALUES (20151022);
INSERT 0 1

--查看数据。
openGauss=# SELECT * FROM test_date;
     dt     
------------
 2020-12-21
 2014-12-21
 2015-10-22
(3 rows)

time 类型输入

支持如下格式:

格式含义
'[-][D] hh:mm:ss[.frac]'时分秒,前方可以指定为负,D 表示天数,取值范围为[0-34]
'[-]hhmmss[.frac]'时分秒
[-]hhmmss[.frac]时分秒

备注:

  • 对于格式 'hh:mm:ss' ,还支持宽松的类型 'hh:mm' 和 'ss' 的输入格式
  • 当输入整数 0 时,代表的值为 '00:00:00',也是 time 类型的零值
  • 由于 time 类型兼容后范围可大于 24 小时,并非仅能表示一天中的时间,请勿将 time 类型转型为 timetz 类型
  • 对于输入值为非法数据场景,dolphin.b_compatibility_mode开启时候,显式转换(如select cast('23:65:66' as time))和function转换(如select time('23:65:66'))场景下返回NULL。dolphin.b_compatibility_mode关闭时则可能会对非法的时间数据按照正常的数据解释处理(如分秒数据超出范围时按往前进位处理等场景)、强制返回0(如insert ignore等场景)处理或者直接提示错误处理(如格式不对等场景)。

示例(注意下方 openGauss 数据库兼容性为 b)

--创建表。
openGauss=# CREATE TABLE test_time(
openGauss(# ti time(2));
CREATE TABLE
    
--插入数据。
openGauss=# INSERT INTO test_time VALUES ('2 9:12:24.1234');
INSERT 0 1
openGauss=# INSERT INTO test_time VALUES ('-34:56:59.1234');
INSERT 0 1
openGauss=# INSERT INTO test_time VALUES (561234);
INSERT 0 1

--查看数据。
openGauss=# SELECT * FROM test_time;
      ti      
--------------
 57:12:24.12
 -34:56:59.12
 56:12:34
(3 rows)

datetime 类型输入

支持如下格式:

格式含义
'YYYY-MM-DD hh:mm:ss[.frac]','YY-MM-DD hh:mm:ss[.frac]'时间戳
'YYYYMMDDhhmmss', 'YYMMDDhhmmss'时间戳
YYYYMMDDhhmmss,YYMMDDhhmmss时间戳

备注:

  • 输入必须为有效日期,不支持月份或者日期为零值,月份或者日期出现为0的时候,表现与mysql在sql_mode中打开NO_ZERO_IN_DATE行为一致,具体见Date类型。
  • openGuass关闭dolphin.sql_mode的no_zero_date时可以兼容年月日均为0的时间,包括兼容年月日为0但时分秒不为0的datetime。
  • 对于'YYYYMMDDhhmmss' 和 'YYMMDDhhmmss' 格式,只有当字符串长度刚好为 8 或者 14 的时候,才会将字符串前4位字母识别为年的部分,其余都只会将前2位字母识别为年的部分
  • 对于输入为 YYYYMMDDhhmmss 或 YYMMDDhhmmss 格式,输入的整数长度应该为 6/8/12/14 其中之一,如果长度不满足这个要求,则相当于往整数前方添加零,直到长度符合 6/8/12/14 其中之一(长度为6对应为YYMMDD格式,长度为8对应为YYYYMMDD格式,长度为12对应为YYMMDDhhmmss格式,长度为14对应为YYYYMMDDhhmmss格式)
  • 类似兼容后的 date 类型,如果要想输入年份大于等于 10000 的时间戳,请使用 'YYYY-MM-DD hh:mm:ss[.frac]' 这种格式
  • 当输入的值发生舍入时,会对舍入后的值进行范围判断,当舍入后的值超出类型范围时,严格模式下报错,非严格模式下告警并返回全零或者NULL。
  • 对于输入值为非法数据场景,dolphin.b_compatibility_mode开启时候,显式转换(如select cast('2022-05-05 1:55:61' as datetime))场景下返回NULL。dolphin.b_compatibility_mode关闭时则可能会对非法的时间数据按照正常的数据解释处理(如分秒数据超出范围时按往前进位处理等场景)、强制返回0(如insert ignore等场景)处理或者直接提示错误处理(如格式不对等场景)。

示例(注意下方 openGauss 数据库兼容性为 b)

--创建表。
openGauss=# CREATE TABLE test_datetime(
openGauss(# dt datetime(2));
CREATE TABLE
    
--插入数据。
openGauss=# INSERT INTO test_datetime VALUES ('2020-11-08 02:31:25.961');
INSERT 0 1
openGauss=# INSERT INTO test_datetime VALUES (201112234512);
INSERT 0 1

--查看数据。
openGauss=# SELECT * FROM test_datetime;
           dt           
------------------------
 2020-11-08 02:31:25.96
 2020-11-12 23:45:12
(2 rows)

timestamp 类型输入

支持如下格式:

格式含义
'YYYY-MM-DD hh:mm:ss[.frac][+/-hh:mm:ss]', 'YY-MM-DD hh:mm:ss[.frac][+/-hh:mm:ss]'带时区信息时间戳
'YYYYMMDDhhmmss[.frac]', 'YYMMDDhhmmss[.frac]'带时区信息时间戳
YYYYMMDDhhmmss[.frac],YYMMDDhhmmss[.frac]带时区信息时间戳

备注:

  • 输入必须为有效日期,不支持月份或者日期为零值,月份或者日期出现为0的时候,表现与mysql在sql_mode中打开NO_ZERO_IN_DATE行为一致,具体见Date类型。
  • openGuass关闭dolphin.sql_mode的no_zero_date时可以兼容年月日均为0的时间,包括兼容年月日为0但时分秒不为0的timestamp。
  • 兼容的 timestamp 类型允许在格式'YYYY-MM-DD hh:mm:ss[.frac]'后面带上时区的偏移信息[+/-hh:mm:ss]
  • 类似兼容后的 date 类型,如果要想输入年份大于等于 10000 的时间戳,请使用 'YYYY-MM-DD hh:mm:ss[.frac]' 这种格式
  • 注意,timestamp 类型在 MySQL 一端为不带时区的时间戳,而在 openGauss 一端为带时区的时间戳,实际上兼容后 timestamp 类型在内部会使用 timestamptz 类型存储,请用户在使用前注意这种区别,如想使用不带时区的时间戳,可以使用 datetime 类型。
  • 注意:由于 MySQL 一端没有 timestamp with[out] time zone 这种语法,但是我们仍然在 openGauss 保留这种语法。timestamp with time zone 等价于直接原来 openGauss timestamptz 类型,timestamp without time zone 等价于直接使用原来 openGauss 中的 timestamp 类型(并非兼容后的 timestamp 类型,是指 openGauss 原有的不带时区属性的 timestamp 类型)
  • 注意:当输入的值发生舍入时,会对舍入后的值进行范围判断,当舍入后的值超出类型范围时,严格模式下报错,非严格模式下告警并返回全零或者NULL。
  • 对于输入值为非法数据场景,dolphin.b_compatibility_mode开启时候,显式转换(如select cast('2022-05-05 1:55:61' as timestamp))和function转换(如select timestamp('2022-05-05 1:55:61'))场景下返回NULL。dolphin.b_compatibility_mode关闭时则可能会对非法的时间数据按照正常的数据解释处理(如分秒数据超出范围时按往前进位处理等场景)、强制返回0(如insert ignore等场景)处理或者直接提示错误处理(如格式不对等场景)。

示例(注意下方 openGauss 数据库兼容性为 b)

--设置时区。
openGauss=# SET TIME ZONE PRC;
SET

--创建表。
openGauss=# CREATE TABLE test_timestamp(
openGauss(# ts timestamp(2));
CREATE TABLE
    
--插入数据。
openGauss=# INSERT INTO test_timestamp VALUES ('2012-10-21 23:55:23-12:12');
INSERT 0 1
openGauss=# INSERT INTO test_timestamp VALUES (201112234512);
INSERT 0 1

--查看数据。
openGauss=# SELECT * FROM test_timestamp;
           ts           
------------------------
 2012-10-22 20:07:23+08
 2020-11-12 23:45:12+08
(2 rows)
    
--变更时区。
openGauss=# SET TIME ZONE UTC;
SET
--查看数据。
openGauss=# SELECT * FROM test_timestamp;
           ts           
------------------------
 2012-10-22 12:07:23+00
 2020-11-12 15:45:12+00
(2 rows)

year/year(4),year(2)类型输入

支持如下格式:

格式含义
'YYYY', 'YY'年,当输入两位数年份时,若值小于70,则实际年份需要加上2000,例如'69'表示2069年;若值大于等于70,则实际年份需要加上1900,例如'70'表示1970年
YYYY, YY

备注

  • 三种类型都接受相同的输入格式和范围,区别仅在于 year(2) 类型输出格式只为 2 位数

  • 如果输入'0',openGauss 会解析成为 2000 年;但是当输入的是整数0,openGauss 会解析成为 0,输出表现为0000,表示 year类型的 0 值

  • MySQL中不支持使用DATE, DATETIME, TIMESTAMP直接赋值到YEAR类型,但是openGuass支持。如下示例Update语句Mysql执行失败,openGauss执行成功。

    create table t1(`year` year, `date` date);
    insert into t1 values ('2024', '2024-01-01');
    
    -- 将DATE类型赋值为YEAR类型,MySQL执行失败,但是openGuass执行成功
    update t1 set `year` = `date`;
    

示例(注意下方 openGauss 数据库兼容性为 b)

--创建表。
openGauss=# CREATE TABLE test_year(
openGauss(#  y year,
openGauss(#  y2 year(2));
CREATE TABLE
    
--插入数据。
openGauss=# INSERT INTO test_year VALUES ('70', '70');
INSERT 0 1
openGauss=# INSERT INTO test_year VALUES ('69', '69');
INSERT 0 1
openGauss=# INSERT INTO test_year VALUES ('2069', '2069');
INSERT 0 1
openGauss=# INSERT INTO test_year VALUES ('1970', '1970');
INSERT 0 1

--查看数据。
openGauss=# SELECT * FROM test_year;
  y   | y2 
------+----
 1970 | 70
 2069 | 69
 2069 | 69
 1970 | 70
(4 rows)
意见反馈
编组 3备份
    openGauss 2024-10-11 00:55:40
    取消