Date and Time Functions

The following describes the date and time function in M-compatible GaussDB:

The conditions where an input parameter of a function in "Functions and Operators" in M-Compatibility Developer Guide can be a time expression are described as follows:
Time expressions (mainly including TEXT, DATETIME, DATE, and TIME) and types that can be implicitly converted to time expressions can be used as input parameters. For example, a number can be implicitly converted to text and then used as a time expression.

However, the effectiveness of such condition depends on the function. For example, the DATEDIFF function is used to calculate only the date difference. Therefore, the time expression is parsed as date. The TIMESTAMPDIFF function is used to calculate the time difference based on UNIT. Therefore, the time expression is parsed as DATE, TIME, or DATETIME based on UNIT.

If a SELECT subquery contains only a time function and the input parameters of the function contain columns in the table, when arithmetic operators (such as +, -, *, /, and the negation operator) are used to calculate the result, the return values of the date and time functions are truncated before the arithmetic operation.

m_db=# CREATE TABLE t1(int_var int);
CREATE TABLE
m_db=# INSERT INTO t1 VALUES(100);
INSERT 0 1
m_db=# SELECT (SELECT (1 * DATE_ADD('2020-10-20', interval int_var microsecond))) AS a FROM t1; -- Truncate is not performed.
       a        
----------------
 20201020000000
(1 row)

m_db=# SELECT (1 * (SELECT DATE_ADD('2020-10-20', interval int_var microsecond))) AS a FROM t1; -- Truncation is performed.
  a   
------
 2020
(1 row)

The m_db=# SELECT 1 * a FROM (SELECT (SELECT 1 * DATE_ADD('2020-10-20', interval int_var microsecond)) AS a FROM t1) AS t2; -- Truncation is not performed.
     1 * a      
----------------
 20201020000000
(1 row)

m_db=# SELECT 1 * a FROM (SELECT (SELECT DATE_ADD('2020-10-20', interval int_var microsecond)) AS a FROM t1) AS t2; -- Truncation is performed.
 1 * a 
-------
  2020
(1 row)

If an input parameter of a function is an invalid date:
Generally, the supported DATE and DATETIME ranges are the same as those in MySQL. The value of DATE ranges from '0000-01-01' to '9999-12-31', and the value of DATETIME ranges from '0000-01-01 00:00:00' to '9999-12-31 23:59:59'. Although the DATE and DATETIME ranges supported by GaussDB are greater than those supported by MySQL, out-of-bounds dates are still invalid.

Most time functions generate alarms and return NULL. Only dates that can be normally converted by CAST are normal and reasonable dates.

In the new framework, most date and time functions in GaussDB are the same as those in MySQL. The following table lists the differences between some functions.

**Table 1** Date and time functions
No.	MySQL	GaussDB	Difference
1	ADDDATE()	Supported	-
2	ADDTIME()	Supported	-
3	CONVERT_TZ()	Supported	-
4	CURDATE()	Supported	-
5	CURRENT_DATE()/CURRENT_DATE	Supported	-
6	CURRENT_TIME()/CURRENT_TIME	Supported, with differences.	The integer value of a MySQL input parameter is wrapped based on the maximum value 255 of one byte, for example, SELECT CURRENT_TIME(257) == SELECT CURRENT_TIME(1). GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.
7	CURRENT_TIMESTAMP()/CURRENT_TIMESTAMP	Supported, with differences.	The integer value of a MySQL input parameter is wrapped based on the maximum value 255 of one byte, for example, SELECT CURRENT_TIMESTAMP(257) == SELECT CURRENT_TIMESTAMP(1). GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.
8	CURTIME()	Supported, with differences.	The integer value of a MySQL input parameter is wrapped based on the maximum value 255 of one byte, for example, SELECT CURTIME(257) == SELECT CURTIME(1). GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.
9	DATE()	Supported	-
10	DATE_ADD()	Supported	-
11	DATE_FORMAT()	Supported	-
12	DATE_SUB()	Supported	-
13	DATEDIFF()	Supported	-
14	DAY()	Supported	-
15	DAYNAME()	Supported	-
16	DAYOFMONTH()	Supported	-
17	DAYOFWEEK()	Supported	-
18	DAYOFYEAR()	Supported	-
19	EXTRACT()	Supported	-
20	FROM_DAYS()	Supported	-
21	FROM_UNIXTIME()	Supported	-
22	GET_FORMAT()	Supported	-
23	HOUR()	Supported	-
24	LAST_DAY()	Supported	-
25	LOCALTIME()/LOCALTIME	Supported, with differences.	The integer value of a MySQL input parameter is wrapped based on the maximum value 255 of one byte, for example, SELECT LOCALTIME(257) == SELECT LOCALTIME(1). GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.
26	LOCALTIMESTAMP/LOCALTIMESTAMP()	Supported, with differences.	The integer value of a MySQL input parameter is wrapped based on the maximum value 255 of one byte, for example, SELECT LOCALTIMESTAMP(257) == SELECT LOCALTIMESTAMP(1). GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.
27	MAKEDATE()	Supported	-
28	MAKETIME()	Supported	-
29	MICROSECOND()	Supported	-
30	MINUTE()	Supported	-
31	MONTH()	Supported	-
32	MONTHNAME()	Supported	-
33	NOW()	Supported, with differences.	The integer value of a MySQL input parameter is wrapped based on the maximum value 255 of one byte, for example, SELECT NOW(257)==SELECT NOW(1). GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.
34	PERIOD_ADD()	Supported, with differences.	Behaviors of integer overflow processing. In MySQL 5.7, the maximum value of an input parameter result of this function is 2^32=4294967296. When the accumulated value of the month corresponding to period and the month_number in the input parameter or result exceed the uint32 range, integer wraparound occurs. This issue has been resolved in MySQL 8.0. The performance of this function in GaussDB is the same as that in MySQL 8.0. Signs of negative period. In MySQL 5.7, a negative year is parsed as an abnormal value instead of an error. An error is reported when a GaussDB input parameter or result is negative (for example, January 100 minus 10000 months). This issue has been resolved in MySQL 8.0. The performance of this function in GaussDB is the same as that in MySQL 8.0. Signs that the month in period exceeds the range. In MySQL 5.7, if the month is greater than 12 or equal to 0, for example, 200013 or 199900, it will be postponed correspondingly to a later year or the 0th month will be processed as December of the previous year. This issue has been resolved in MySQL 8.0. An error is reported when the month is beyond the range. The performance of this function in GaussDB is the same as that in MySQL 8.0.
35	PERIOD_DIFF()	Supported, with differences.	Behaviors of integer overflow processing. In MySQL 5.7, the maximum value of an input parameter result of this function is 2^32=4294967296. When the accumulated value of the month corresponding to period and the month_number in the input parameter or result exceed the uint32 range, integer wraparound occurs. This issue has been resolved in MySQL 8.0. The performance of this function in GaussDB is the same as that in MySQL 8.0. Signs of negative period. In MySQL 5.7, a negative year is parsed as an abnormal value instead of an error. An error is reported when a GaussDB input parameter or result is negative (for example, January 100 minus 10000 months). This issue has been resolved in MySQL 8.0. An error is reported when the month is beyond the range. The performance of this function in GaussDB is the same as that in MySQL 8.0. Signs that the month in period exceeds the range. In MySQL 5.7, if the month is greater than 12 or equal to 0, for example, 200013 or 199900, it will be postponed correspondingly to a later year or the 0th month will be processed as December of the previous year. This issue has been resolved in MySQL 8.0. An error is reported when the month is beyond the range. The performance of this function in GaussDB is the same as that in MySQL 8.0.
36	QUARTER()	Supported	-
37	SEC_TO_TIME()	Supported	-
38	SECOND()	Supported	-
39	STR_TO_DATE()	Supported	Return value difference: In GaussDB, text is returned. In MySQL, datetime or date is returned.
40	SUBDATE()	Supported	-
41	SUBTIME()	Supported	-
42	SYSDATE()	Supported, with differences.	The integer value of the MySQL input parameter is wrapped when reaching the maximum value 255 by one byte. GaussDB does not support wraparound.
43	TIME()	Supported	-
44	TIME_FORMAT()	Supported	-
45	TIME_TO_SEC()	Supported	-
46	TIMEDIFF()	Supported	-
47	TIMESTAMP()	Supported	-
48	TIMESTAMPADD()	Supported	-
49	TIMESTAMPDIFF()	Supported	-
50	TO_DAYS()	Supported	-
51	TO_SECONDS()	Supported	In MySQL 5.7, the precision of this function is incorrect. When the precision transfer parameter is enabled, the GaussDB precision information is normal and consistent with that in MySQL 8.0.
52	UNIX_TIMESTAMP()	Supported	MySQL determines whether to return a fixed-point value or an integer based on whether an input parameter contains decimal places. When operators or functions are nested in the input parameter, GaussDB may return a value of the type different from that in MySQL. If the inner node returns a value of the fixed-point, floating-point, sting, or time type (excluding the DATE type), MySQL may return an integer, and GaussDB returns a fixed-point value.
53	UTC_DATE()	Supported	-
54	UTC_TIME()	Supported, with differences.	The integer value of a MySQL input parameter is wrapped based on the maximum value 255 of one byte. GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.
55	UTC_TIMESTAMP()	Supported, with differences.	The integer value of a MySQL input parameter is wrapped based on the maximum value 255 of one byte. GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.
56	WEEK()	Supported	-
57	WEEKDAY()	Supported	-
58	WEEKOFYEAR()	Supported	-
59	YEAR()	Supported	-
60	YEARWEEK()	Supported	-