Updated on 2025-06-30 GMT+08:00

Date and Time Functions

The date and time functions in the M-compatible mode in GaussDB, with the same behavior as MySQL, are described as follows:

  • Functions may use time expressions as their input parameters.

    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, different functions take effect in different ways. For example, the DATEDIFF function calculates only the difference between dates. Therefore, the time expression is parsed as the date type. The TIMESTAMPDIFF function parses the time expression as DATE, TIME, or DATETIME based on the UNIT parameter before calculating the time difference.

  • The input parameters of functions may contain 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.

    In most cases, time functions report an alarm and return NULL if the input date is invalid, unless the invalid date can be converted by CAST.

Most date and time functions in the GaussDB M-compatible framework are the same as those in MySQL. The following table lists the differences between them in terms of some functions.

Table 1 Date and time functions

MySQL

GaussDB

Difference

ADDDATE()

Supported

-

ADDTIME()

Supported

-

CONVERT_TZ()

Supported

-

CURDATE()

Supported

-

CURRENT_DATE()/CURRENT_DATE

Supported

-

CURRENT_TIME()/CURRENT_TIME

Supported, with differences.

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

Supported, with differences.

In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value), 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.

CURTIME()

Supported, with differences.

In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value), 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.

DATE()

Supported

-

DATE_ADD()

Supported

-

DATE_FORMAT()

Supported

-

DATE_SUB()

Supported

-

DATEDIFF()

Supported

-

DAY()

Supported

-

DAYNAME()

Supported

-

DAYOFMONTH()

Supported

-

DAYOFWEEK()

Supported

-

DAYOFYEAR()

Supported

-

EXTRACT()

Supported

-

FROM_DAYS()

Supported

-

FROM_UNIXTIME()

Supported

-

GET_FORMAT()

Supported

-

HOUR()

Supported

-

LAST_DAY()

Supported

-

LOCALTIME()/LOCALTIME

Supported, with differences.

In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value), 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.

LOCALTIMESTAMP/LOCALTIMESTAMP()

Supported, with differences.

In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value), 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.

MAKEDATE()

Supported

-

MAKETIME()

Supported, with differences.

In the distributed pushdown scenario, if no second precision is specified for the TIME type, MySQL supplements six trailing zeros by default, but GaussDB does not supplement anything.

MICROSECOND()

Supported

-

MINUTE()

Supported

-

MONTH()

Supported

-

MONTHNAME()

Supported

-

NOW()

Supported, with differences.

In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value), 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.

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 value 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 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). 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 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. GaussDB reports an error for months beyond the range. 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.

PERIOD_DIFF()

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 value 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 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). 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 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. GaussDB reports an error for months beyond the range. 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.

QUARTER()

Supported

-

SEC_TO_TIME()

Supported

-

SECOND()

Supported

-

STR_TO_DATE()

Supported, with differences.

GaussDB returns values of the text type, while MySQL returns values of the datetime or date type.

SUBDATE()

Supported

-

SUBTIME()

Supported

-

SYSDATE()

Supported, with differences.

In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value).

GaussDB does not support wraparound.

TIME()

Supported

-

TIME_FORMAT()

Supported

-

TIME_TO_SEC()

Supported

-

TIMEDIFF()

Supported

-

TIMESTAMP()

Supported

-

TIMESTAMPADD()

Supported

-

TIMESTAMPDIFF()

Supported

-

TO_DAYS()

Supported

-

TO_SECONDS()

Supported

-

UNIX_TIMESTAMP()

Supported, with differences.

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()

Supported

-

UTC_TIME()

Supported, with differences.

In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value). GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.

UTC_TIMESTAMP()

Supported, with differences.

In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value). GaussDB supports only valid values ranging from 0 to 6. For other values, an error is reported.

WEEK()

Supported

-

WEEKDAY()

Supported

-

WEEKOFYEAR()

Supported

-

YEAR()

Supported

-

YEARWEEK()

Supported

-