Help Center/ GaussDB/ MySQL Compatibility(Distributed)/ MySQL Compatibility in M-compatible Mode/ System Functions/ Date and Time Functions
Updated on 2024-12-06 GMT+08:00
View PDF
Share

Date and Time Functions

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

  • Functions listed in "Functions and Operators" in M-Compatibility Developer Guide may use time expressions in their input parameters.

    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, time expressions varies depending on functions. For example, the DATEDIFF function is used to calculate only the date difference. Therefore, its time expression is parsed as date. The TIMESTAMPDIFF function is used to calculate the time difference based on UNIT. Therefore, its time expression may be parsed as DATE, TIME, or DATETIME based on UNIT.

  • 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 GaussDB supports larger ranges, dates beyond the MySQL ranges are still considered 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 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, but 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 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.
  1. 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.

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

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