# 日期和时间函数 以下为GaussDB数据库M-Compatibility兼容性日期时间函数公共说明，与MySQL行为一致。 - 函数入参为时间类型表达式的情况： 时间类型表达式主要包括TEXT、DATETIME、DATE或TIME，但所有可以隐式转换为时间表达式的类型都可以作为入参，比如数字类型可以通过先隐式转化为TEXT，再作为时间类型表达式生效。 但是，不同函数的具体生效情况会有所不同。例如：DATEDIFF函数仅计算日期之间的差值，因此时间表达式会被解析为日期；而TIMESTAMPDIFF函数在计算时间差值时，会根据UNIT参数来决定将时间表达式解析为DATE、TIME或 DATETIME。 - 函数入参为无效日期的情况： 一般而言，日期时间函数支持DATE、DATETIME的范围和MySQL保持一致。DATE支持的范围为'0000-01-01'到'9999-12-31'，DATETIME支持的范围为'0000-01-01 00:00:00'到'9999-12-31 23:59:59'。虽然GaussDB支持的DATE、DATETIME范围大于MySQL，但是越界仍然算无效日期。 大部分时间函数对于入参为无效时间时，会告警并返回NULL，只有能通过cast正常转换的日期，才是正常合理的日期。 GaussDB M-Compatibility兼容性框架下GaussDB的大部分日期时间函数与MySQL一致，一些函数的差异如[表1]所示： 表1日期和时间函数列表 | 函数名 | 与MySQL的差异 | |:---|:---| | ADDDATE() | - | | ADDTIME() | - | | CONVERT_TZ() | - MySQL支持使用'system'指定时区为系统时区，GaussDB不支持使用'system'指定时区。 - CONVERT_TZ执行转换前，会校验时间是否合法且在TIMESTAMP类型范围内（'1970-01-01 00:00:01' UTC \~ '2038-01-19 03:14:07' UTC），时间合法但不在范围内则不转换，返回原值。MySQL校验第一个入参经第二个入参（from_tz）转换为UTC后的值是否满足要求，GaussDB则仅校验第一个入参的原始值。 ``` -- GaussDB m_db=# SELECT convert_tz('1970-01-01 00:00:01', '-01:00', '+01:00'), convert_tz('1970-01-01 00:00:01', '+01:00', '+03:00'); convert_tz | convert_tz ---------------------+--------------------- 1970-01-01 02:00:01 | 1970-01-01 02:00:01 (1 row) m_db=# SELECT convert_tz('1970-01-01 00:00:00', '-01:00', '+01:00'), convert_tz('1970-01-01 00:00:00', '+01:00', '+03:00'); convert_tz | convert_tz ---------------------+--------------------- 1970-01-01 00:00:00 | 1970-01-01 00:00:00 (1 row) -- MySQL mysql> SELECT convert_tz('1970-01-01 00:00:01', '-01:00', '+01:00'), convert_tz('1970-01-01 00:00:01', '+01:00', '+03:00'); +-------------------------------------------------------+-------------------------------------------------------+ | convert_tz('1970-01-01 00:00:01', '-01:00', '+01:00') | convert_tz('1970-01-01 00:00:01', '+01:00', '+03:00') | +-------------------------------------------------------+-------------------------------------------------------+ | 1970-01-01 02:00:01 | 1970-01-01 00:00:01 | +-------------------------------------------------------+-------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT convert_tz('1970-01-01 00:00:00', '-01:00', '+01:00'), convert_tz('1970-01-01 00:00:00', '+01:00', '+03:00'); +-------------------------------------------------------+-------------------------------------------------------+ | convert_tz('1970-01-01 00:00:00', '-01:00', '+01:00') | convert_tz('1970-01-01 00:00:00', '+01:00', '+03:00') | +-------------------------------------------------------+-------------------------------------------------------+ | 1970-01-01 02:00:00 | 1970-01-01 00:00:00 | +-------------------------------------------------------+-------------------------------------------------------+ 1 row in set (0.00 sec) ``` | | CURDATE() | - | | CURRENT_DATE()/CURRENT_DATE | - | | CURRENT_TIME()/CURRENT_TIME | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | CURRENT_TIMESTAMP()/CURRENT_TIMESTAMP | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | CURTIME() | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | LOCALTIME()/LOCALTIME | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | LOCALTIMESTAMP/LOCALTIMESTAMP() | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | NOW() | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | SYSDATE() | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | UTC_TIME() | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | UTC_TIMESTAMP() | MySQL入参整型值会按照一字节最大值255整数回绕（例：SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1)）。 GaussDB只支持\[0,6\]合法值，其他值报错。 | | DATE() | - | | DATE_ADD() | - | | DATE_FORMAT() | - | | DATE_SUB() | - | | DATEDIFF() | - | | DAY() | - | | DAYNAME() | - | | DAYOFMONTH() | - | | DAYOFWEEK() | - | | DAYOFYEAR() | - | | EXTRACT() | - 当EXTRACT(unit FROM date)中unit入参的取值为DAY_HOUR、DAY_MINUTE、DAY_SECOND或DAY_MICROSECOND时GaussDB会将date入参按datetime类型的格式要求进行解析，MySQL会将date入参按time类型的格式要求进行解析。 ``` -- GaussDB m_db=# SELECT EXTRACT(DAY_HOUR FROM '10:30:45') AS res; WARNING: Incorrect datetime value: '10:30:45' CONTEXT: referenced column: res res ----- (1 row) -- MySQL mysql> SELECT EXTRACT(DAY_HOUR FROM '10:30:45') AS res; +------+ | res | +------+ | 10 | +------+ 1 row in set (0.00 sec) ``` - 当EXTRACT(unit FROM date)中date入参的类型为datetime类型并且unit入参的取值为DAY_HOUR、DAY_MINUTE、DAY_SECOND或DAY_MICROSECOND时，GaussDB的结果都会返回DAY的部分，MySQL的结果只有在date入参为表数据时会返回DAY的部分，例如： ``` -- GaussDB的行为： m_db=# DROP TABLE IF EXISTS t_datetime; DROP TABLE m_db=# CREATE TABLE t_datetime(c1 datetime(6)); CREATE TABLE m_db=# INSERT INTO t_datetime VALUES('2023-01-27 10:11:12.400000'); INSERT 0 1 -- date入参为表数据时，结果会返回DAY的部分。 m_db=# SELECT EXTRACT(DAY_MICROSECOND FROM c1) FROM t_datetime; extract ---------------- 27101112400000 (1 row) -- date入参为常量数据时，结果也会返回DAY的部分。 m_db=# SELECT EXTRACT(DAY_MICROSECOND FROM cast('2023-01-27 10:11:12.400000' as datetime(6))); extract ---------------- 27101112400000 (1 row) m_db=# DROP TABLE IF EXISTS t_datetime; DROP TABLE -- MySQL的行为： mysql> DROP TABLE IF EXISTS t_datetime; Query OK, 0 rows affected (0.04 sec) mysql> CREATE TABLE t_datetime(c1 datetime(6)); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t_datetime VALUES('2023-01-27 10:11:12.400000'); Query OK, 1 row affected (0.00 sec) -- date入参为表数据时，结果会返回DAY的部分。 mysql> SELECT EXTRACT(DAY_MICROSECOND FROM c1) FROM t_datetime; +----------------------------------+ | EXTRACT(DAY_MICROSECOND FROM c1) | +----------------------------------+ | 27101112400000 | +----------------------------------+ 1 row in set (0.01 sec) -- date入参为常量数据时，结果不会返回DAY的部分。 mysql> SELECT EXTRACT(DAY_MICROSECOND FROM cast('2023-01-27 10:11:12.400000' as datetime(6))); +---------------------------------------------------------------------------------+ | EXTRACT(DAY_MICROSECOND FROM cast('2023-01-27 10:11:12.400000' as datetime(6))) | +---------------------------------------------------------------------------------+ | 101112400000 | +---------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS t_datetime; Query OK, 0 rows affected (0.04 sec) ``` | | FROM_DAYS() | - | | FROM_UNIXTIME() | - | | GET_FORMAT() | - | | HOUR() | - | | LAST_DAY() | - | | MAKEDATE() | - | | MAKETIME() | 分布式下推场景下当TIME类型秒位无精度时，MYSQL默认补齐6个0，GaussDB不做补齐。 | | MICROSECOND() | - | | MINUTE() | - | | MONTH() | - | | MONTHNAME() | - | | PERIOD_ADD() | MySQL 8.0修复了以下问题，在以下场景中，该函数的行为与MySQL 8.0版本保持一致，与MySQL 5.7版本存在差异： - 整数溢出处理的行为： MySQL在5.7版本，此函数入参和结果的最大值都为2\^32=4294967296，在入参或结果的period对应的月份累加值以及month_number超过uint32范围时存在整数回绕问题。GaussDB不存在该问题。 - 负数period的表现： MySQL在5.7版本，会将负数年份解析为异常值而不是报错。GaussDB入参或结果（如100年1月减去10000月）出现负数时报错。 - period月份越界的表现： MySQL在5.7版本中，若月份大于12或等于0，例如200013、199900，会将其顺延到之后的年份，或者将0月作为上一年12月处理。GaussDB会对越界月份进行报错。 - 入参无法严格转换为整数时的行为： MySQL在5.7版本中，当传入的参数无法严格转换为整数时，会将其强制转换为整数值，GaussDB进行报错。 ``` -- GaussDB m_db=# SELECT period_add('20.1', 1); WARNING: The INTEGER value '20.1' is incorrect. CONTEXT: referenced column: period_add ERROR: Incorrect arguments to period_add CONTEXT: referenced column: period_add m_db=# SELECT period_add('20ab', 1); WARNING: The INTEGER value '20ab' is incorrect. CONTEXT: referenced column: period_add ERROR: Incorrect arguments to period_add CONTEXT: referenced column: period_add -- MySQL mysql> SELECT period_add('20.1', 1); +-----------------------+ | period_add('20.1', 1) | +-----------------------+ | 200109 | +-----------------------+ 1 row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect INTEGER value: '20.1' mysql> SELECT period_add('20ab', 1); +-----------------------+ | period_add('20ab', 1) | +-----------------------+ | 200109 | +-----------------------+ 1 row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect INTEGER value: '20ab' ``` | | PERIOD_DIFF() | MySQL 8.0修复了以下问题，在以下场景中，该函数的行为与MySQL 8.0版本保持一致，与MySQL 5.7版本存在差异： - 整数溢出处理的行为： MySQL在5.7版本，此函数入参和结果的最大值都为2\^32=4294967296，在入参或结果的period对应的月份累加值以及month_number超过uint32范围时存在整数回绕问题。GaussDB不存在该问题。 - 负数period的表现： MySQL在5.7版本，会将负数年份解析为异常值而不是报错。GaussDB入参或结果（如100年1月减去10000月）出现负数时报错。 - period月份越界的表现： MySQL在5.7版本中，若月份大于12或等于0，例如200013、199900，会将其顺延到之后的年份，或者将0月作为上一年12月处理。GaussDB会对越界月份进行报错。 - 入参无法严格转换为整数时的行为： MySQL在5.7版本中，当传入的参数无法严格转换为整数时，会将其强制转换为整数值，GaussDB进行报错。 ``` -- GaussDB m_db=# SELECT period_add('20.1', 1); WARNING: The INTEGER value '20.1' is incorrect. CONTEXT: referenced column: period_add ERROR: Incorrect arguments to period_add CONTEXT: referenced column: period_add m_db=# SELECT period_add('20ab', 1); WARNING: The INTEGER value '20ab' is incorrect. CONTEXT: referenced column: period_add ERROR: Incorrect arguments to period_add CONTEXT: referenced column: period_add -- MySQL mysql> SELECT period_add('20.1', 1); +-----------------------+ | period_add('20.1', 1) | +-----------------------+ | 200109 | +-----------------------+ 1 row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect INTEGER value: '20.1' mysql> SELECT period_add('20ab', 1); +-----------------------+ | period_add('20ab', 1) | +-----------------------+ | 200109 | +-----------------------+ 1 row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect INTEGER value: '20ab' ``` | | QUARTER() | - | | SEC_TO_TIME() | - | | SECOND() | - | | STR_TO_DATE() | 返回值类型与MySQL有差异，GaussDB返回的是text，MySQL返回的是datetime、date。 | | SUBDATE() | - | | SUBTIME() | - | | TIME() | - | | TIME_FORMAT() | - | | TIME_TO_SEC() | - | | TIMEDIFF() | - | | TIMESTAMP() | - | | TIMESTAMPADD() | - | | TIMESTAMPDIFF() | - | | TO_DAYS() | - | | TO_SECONDS() | - | | UNIX_TIMESTAMP() | MySQL会根据入参是否存在小数位，决定返回定点型还是整型。当前GaussDB在内层嵌套操作符或函数时，返回的类型与MySQL可能存在不同。当内层节点返回定点、浮点、字符型、时间类型（不包括DATE类型）时，MySQL可能返回整型，GaussDB会返回定点型。 | | UTC_DATE() | - | | WEEK() | - | | WEEKDAY() | - | | WEEKOFYEAR() | - | | YEAR() | - | | YEARWEEK() | - |