Date and Time Functions

Updated on 2024-12-06 GMT+08:00

View PDF

NOTE:

The following describes the date and time functions 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 the 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.

Time functions may trigger alarms and return NULL unless the input parameters can be properly converted into dates by CAST.

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 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.
7	CURRENT_TIMESTAMP()/CURRENT_TIMESTAMP	Supported, with differences.	The integer value of a MySQL input parameter is wrapped when it reaches 255, 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 when it reaches 255, 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 when it reaches 255, 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 when it reaches 255, 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 when it reaches 255, 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.	Processing of 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 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. Performance 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 it reaches 255. 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, string, 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 when it reaches 255. 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 when it reaches 255. 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	-

Parent topic: System Functions

Previous topic: Flow Control Functions

Next topic: String Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot