Date and Time Functions

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

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)

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)

**Table 1** Date and time functions
Function	Differences Compared with MySQL
ADDDATE()	-
ADDTIME()	-
CONVERT_TZ()	-
CURDATE()	-
CURRENT_DATE()/CURRENT_DATE	-
CURRENT_TIME()/CURRENT_TIME	In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value), 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.
CURRENT_TIMESTAMP()/CURRENT_TIMESTAMP
CURTIME()
LOCALTIME()/LOCALTIME
LOCALTIMESTAMP/LOCALTIMESTAMP()
NOW()
SYSDATE()
UTC_TIME()
UTC_TIMESTAMP()
DATE()	-
DATE_ADD()	-
DATE_FORMAT()	-
DATE_SUB()	-
DATEDIFF()	-
DAY()	-
DAYNAME()	-
DAYOFMONTH()	-
DAYOFWEEK()	-
DAYOFYEAR()	-
EXTRACT()	-
FROM_DAYS()	-
FROM_UNIXTIME()	-
GET_FORMAT()	-
HOUR()	-
LAST_DAY()	-
MAKEDATE()	-
MAKETIME()	-
MICROSECOND()	-
MINUTE()	-
MONTH()	-
MONTHNAME()	-
PERIOD_ADD()	In MySQL 8.0, the aforementioned issues have been resolved; consequently, the behavior of this function remains consistent with that of MySQL 8.0 in the following scenarios: Processing integer overflow: 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 value of month_number in the input parameter or result exceed the uint32 range, integer wraparound occurs. Performance when the value of period is negative: In MySQL 5.7, a negative year is parsed as an abnormal value instead of an error. Conversely, GaussDB reports an error when any input parameter or result is negative (for example, January 100 minus 10000 months). Performance when the month in period exceeds the range: When dealing with a month greater than 12 or equal to 0, for example, 200013 or 199900, MySQL 5.7 postpones it to the next year or views month 0 as December of the previous year.
PERIOD_DIFF()
QUARTER()	-
SEC_TO_TIME()	-
SECOND()	-
STR_TO_DATE()	-
SUBDATE()	-
SUBTIME()	-
TIME()	-
TIME_FORMAT()	-
TIME_TO_SEC()	-
TIMEDIFF()	-
TIMESTAMP()	-
TIMESTAMPADD()	-
TIMESTAMPDIFF()	-
TO_DAYS()	-
TO_SECONDS()	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.
UNIX_TIMESTAMP()	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, string, or time type (excluding the DATE type), MySQL may return an integer, while GaussDB returns a fixed-point value.
UTC_DATE()	-
WEEK()	-
WEEKDAY()	-
WEEKOFYEAR()	-
YEAR()	-
YEARWEEK()	-