不同数据库模式下 DATE 类型的行为解析

​​​​​​​​​​​​摘要：本文章主要介绍了 GaussDB(DWS)数据类型中的 DATE 类型在不同数据库模式下且在不同应用场景下的行为表现及对比。

本文分享自华为云社区《GaussDB(DWS)数据类型之DATE类型》，原文作者：小仲。

GaussDB(DWS)中有三种模式，分别是 Oracle 模式，TD 模式和 MySQL 模式。不同的类型在不同的模式下，也有不同的行为表现。接下来便对 GaussDB(DWS)中的日期 DATE 类型进行详细介绍。

DATE 类型的取值范围：

GaussDB(DWS)在存储 DATE 类型时，不支持年月日全部为零或部分为零的场景，且取值范围在三种模式下是不同的：

在 Oracle 模式下，DATE 类型的取值范围是公元前 4713 年 1 月 1 日到公元 294277 年 1 月 1 日。在 MySQL 模式和 TD 模式中，DATE 类型的取值范围是公元前 4713 年到公元 5874897 年。

用例说明：

1. 不同模式下 DATE 类型的行为

Oracle 模式

a.DATE 类型的表现

GaussDB(DWS)中 DATE 类型存储时不带时分秒，由于在 Oracle 模式下的 DATE 类型需要带有时分秒，使得 GaussDB(DWS)在 Oracle 模式下存在带时分秒的和不带时分秒的两种 DATE 类型，所以为了容易区分，在显示时不带时分秒的 DATE 类型用列名 date 表示；带时分秒的 DATE 类型用同样带时分秒的 TIMESTAMP 类型的列名 timestamp 表示。

postgres=# select date '4713-01-01 BC';       timestamp        ------------------------ 4713-01-01 00:00:00 BC(1 row)	postgres=# select date '294277-01-01';       timestamp       ----------------------- 294277-01-01 00:00:00(1 row)

​b. Oracle 模式下得到只含有年月日的 DATE 类型的方法

如果想要在 Oracle 模式下将日期转成只有年月日的 DATE 类型，有方法可以实现么？事实上也是有的。GaussDB(DWS)提供了两种方式，一种是通过在 Oracle 模式下使用 date 函数：date 函数的参数至少需要包含年月日，并将日期中的年月日提取并输出；另一种则是通过 cast 函数进行转换，但转换时应将 date 加上双引号，否则转换后的数据还是会含有时分秒部分。

postgres=# select date('2008-05-24 10:40:21');    date    ------------ 2008-05-24(1 row)postgres=# select cast('2008-05-24 10:40:21.100050' as "date");    date    ------------ 2008-05-24(1 row)postgres=# select cast('2008-05-24 10:40:21.100050' as date);      timestamp      --------------------- 2008-05-24 10:40:21(1 row)

​c.DATE 类型操作符

MySQL 模式和 TD 模式： DATE 类型只有年月日。

a. DATE 类型的表现

MySQL 模式与 TD 模式下 DATE 类型无差异。

mysql_db=# select date '5874897-01-01';
     date     
---------------
 5874897-01-01
(1 row)
td_db=# select date '5874897-01-01';
     date     
---------------
 5874897-01-01
(1 row)

​b. MySQL 模式与 TD 模式下得到只含有年月日的 DATE 类型的方法

由于在 MySQL 模式或 TD 模式下，DATE 类型只有年月日，故使用 to_date 函数和 date 函数所得到的结果都只有年月日，同时 cast 函数进行类型转换时，date 加不加引号所得到的结果也是一致的。

td_db=# select date('2008-05-24 10:40:21');    date    ------------ 2008-05-24(1 row)td_db=# select cast('2008-05-24 10:40:21.100050' as "date");    date    ------------ 2008-05-24(1 row)td_db=# select cast('2008-05-24 10:40:21.100050' as date);    date    ------------ 2008-05-24(1 row)
mysql_db=# select date('2008-05-24 10:40:21');    date    ------------ 2008-05-24(1 row)mysql_db=# select cast('2008-05-24 10:40:21.100050' as "date");    date    ------------ 2008-05-24(1 row)mysql_db=# select cast('2008-05-24 10:40:21.100050' as date);    date    ------------ 2008-05-24(1 row)

​c. DATE 类型操作符

与 Oracle 模式的差异：当 DATE 类型的值进行相减时返回值不带 day，只有数值。

2. 不同模式下的 DATE 类型相关函数结果的行为：

GaussDB(DWS)中存在与 DATE 类型相关的函数：

• to_date：将文本类型的值转换为指定格式的时间戳，若参数只有一个时，默认格式为 DATE 类型的格式。

• to_char：将一个 DATE、TIMESTAMP、TIMESTAMP WITHTIME ZONE 或者 TIMESTAMP WITH LOCAL TIME ZONE 类型的 DATETIME 或者 INTERVAL 值按照 fmt 指定的格式转换为 VARCHAR 类型。可选参数 fmt 可以为以下几类：日期、时间、星期、季度和世纪。每类都可以有不同的模板，模板之间可以合理组合；常见的模板有：HH、MM、SS、YYYY、MM、DD。模板可以有修饰词；常用的修饰词是 FM，可以用来抑制前导的零或尾随的空白。返回值类型：varchar

• current_date：获取当前日期。

• date_part：获取给定日期时间中的指定域值。

• isfinite：测试给定日期是否为有效日期。

​Oracle 模式：由于 Oracle 模式中的 DATE 类型含有时分秒，故 to_date 和 to_char 函数得到的结果含有时分秒。

TD 模式：由于 TD 模式下的 DATE 类型只有年月日，故 to_date 函数得到的结果只有年月日。在 TD 模式下，to_char 函数的输出结果可以由 GUC 参数 convert_empty_str_to_null_td 控制，若不设置参数则返回上表中的结果，若设置参数则返回结果与 TD 数据库中的结果保持一致，为 2008/05/24。

MySQL 模式：由于 MySQL 模式下 DATE 类型只有年月日，故 to_date 函数和 to_char 函数的返回结果只有年月日。

想了解 GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的 PB 级数仓黑科技，后台还可获取众多学习资料哦~

点击关注，第一时间了解华为云新鲜技术~