Help Center/ GaussDB/ Service Overview/ Compatibility with MySQL Databases/ Distributed Instances/ MySQL Compatibility in M-compatible Mode/ System Functions/ Date and Time Functions
Updated on 2024-11-11 GMT+08:00
View PDF
Share

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

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

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.
  1. 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.

  2. 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.

  3. 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. 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.

35

PERIOD_DIFF()

Supported, with differences.
  1. 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.

  2. 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.

  3. 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. 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.

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

-

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

-
Parent topic: System Functions