Date and Time Functions

The following describes the date and time function compatibility between GaussDB and MySQL:

In Developer Guide, if an input parameter of a function is a time expression:
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 effective mode varies according to the function. For example, DATEDIFF is used to calculate only the date difference. Therefore, the time expression is parsed as date. TIMESTAMPDIFF 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.

Separators for input parameters of functions:
For a time function, all non-digit characters are regarded as separators when input parameters are processed. The standard format is recommended: Use hyphens (-) to separate year, month, and day, use colons (:) to separate hour, minute, and second, and use a period (.) before milliseconds.

Error-prone scenario: "SELECT timestampdiff(hour, '2020-03-01 00:00:00', '2020-02-28 00:00:00+08');" In B-compatible databases, the time zone in a time function is not automatically calculated. Therefore, +08 is not identified as the time zone. Instead, + is used as a separator and is calculated as seconds.

Most function scenarios of GaussDB date and time functions are the same as those of MySQL, but there are still differences. Some differences are as follows:

If an input parameter of a function is NULL, the function returns NULL, and no warning or error is reported. These functions include:
from_days, date_format, str_to_date, datediff, timestampdiff, date_add, subtime, month, time_to_sec, to_days, to_seconds, dayname, monthname, convert_tz, sec_to_time, addtime, adddate, date_sub, timediff, last_day, weekday, from_unixtime, unix_timestamp, subdate, day, year, weekofyear, dayofmonth, dayofyear, week, yearweek, dayofweek, time_format, hour, minute, second, microsecond, quarter, get_format, extract, makedate, period_add, timestampadd, period_diff, utc_time, utc_timestamp, maketime, and curtime.

Example:
```
gaussdb=# select day(null);
 day 
-----

(1 row)
```
Some functions with pure numeric input parameters are different from those of MySQL. Numeric input parameters without quotation marks are converted into text input parameters for processing.
Example:
```
gaussdb=# select day(19231221.123141);
WARNING:  Incorrect datetime value: "19231221.123141"
CONTEXT:  referenced column: day
 day
-----

(1 row)
```
Time and date calculation functions are adddate, subdate, date_add, and date_sub. If the calculation result is a date, the supported range is [0000-01-01,9999-12-31]. If the calculation result is a date and time, the supported range is [0000-01-01 00:00:00.000000,9999-12-31 23:59:59.999999]. If the calculation result exceeds the supported range, an ERROR is reported in strict mode, or a WARNING is reported in loose mode. If the date result after calculation is within the range [0000-01-01,0001-01-01], GaussDB returns the result normally. MySQL returns '0000-00-00'.
Example:
```
gaussdb=# select subdate('0000-01-01', interval 1 hour);
ERROR:  Datetime function: datetime field overflow
CONTEXT:  referenced column: subdate

gaussdb=# select subdate('0001-01-01', interval 1 day);
   subdate
-------------
 0000-12-31

(1 row)
```
If the input parameter of the date or datetime type of the date and time function contains month 0 or day 0, the value is invalid. In strict mode, an error is reported. In loose mode, if the input is a character string or number, a warning is reported. If the input is of the date or datetime type, the system processes the input as December of the previous year or the last day of the previous month.
If the type of the CAST function is converted to date or datetime, an error is reported in strict mode. In loose mode, no warning is reported. Instead, the system processes the input as December of the previous year or the last day of the previous month. Pay attention to this difference. MySQL outputs the value as it is, even if the year, month, and day are set to 0.

Example:
```
gaussdb=# select adddate('2023-01-00', 1);-- Strict mode
ERROR:  Incorrect datetime value: "2023-01-00"
CONTEXT:  referenced column: adddate

gaussdb=# select adddate('2023-01-00', 1);-- Loose mode
WARNING:  Incorrect datetime value: "2023-01-00"
CONTEXT:  referenced column: adddate
 adddate 
---------

(1 row)

gaussdb=# select adddate(date'2023-00-00', 1);-- Loose mode
  adddate   
------------
 2022-12-01
(1 row)

gaussdb=# select cast('2023/00/00' as date);-- Loose mode
    date    
------------
 2022-11-30
(1 row)

gaussdb=# select cast('0000-00-00' as datetime);-- Loose mode
      timestamp      
---------------------
 0000-00-00 00:00:00
(1 row)
```
If the input parameter of the function is of the numeric data type, no error is reported in the case of invalid input, and the input parameter is processed as 0.
Example:
```
gaussdb=#  select from_unixtime('aa');
    from_unixtime
---------------------
 1970-01-01 08:00:00
(1 row)
```

A maximum of six decimal places are allowed. Decimal places with all 0s are not allowed.

Example:

gaussdb=#  select from_unixtime('1234567899.00000');
    from_unixtime
---------------------
 2009-02-14 07:31:39
(1 row)

If the time function parameter is a character string, the result is correct only when the year, month, and day are separated by a hyphen (-) and the hour, minute, and second are separated by a colon (:).
Example:
```
gaussdb=#  select adddate('20-12-12',interval 1 day);
  adddate   
------------
 2020-12-13
(1 row)
```

If the return value of a function is of the varchar type in MySQL, the return value of the function is of the text type in GaussDB.

-- Return value of a function in GaussDB.
gaussdb=#  SELECT pg_typeof(adddate('2023-01-01', 1));
 pg_typeof 
-----------
 text
(1 row)

-- Return value of a function in MySQL.
mysql> CREATE VIEW v1 AS SELECT adddate('2023-01-01', 1);
Query OK, 0 rows affected (0.00 sec)

mysql> DESC v1;
+--------------------------+-------------+------+-----+---------+-------+
| Field                    | Type        | Null | Key | Default | Extra |
+--------------------------+-------------+------+-----+---------+-------+
| adddate('2023-01-01', 1) | varchar(29) | YES  |     | NULL    |       |
+--------------------------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

**Table 1** Date and time functions
No.	MySQL	GaussDB	Difference
1	ADDDATE()	Supported	The performance of this function is different from that of MySQL due to interval expression differences. For details, see INTERVAL.
2	ADDTIME()	Supported	MySQL returns NULL if the second input parameter is a string in the DATETIME format. GaussDB can calculate the value. The value range of an input parameter is ['0001-01-01 00:00:00', 9999-12-31 23:59:59.999999]. If the first parameter of the ADDTIME function in MySQL is a dynamic parameter (for example, in a prepared statement), the return type is TIME. Otherwise, the parse type of the function is derived from the parse type of the first parameter. The return value rules of the ADDTIME function in GaussDB are as follows: The first input parameter is of the date type, the second input parameter is of the date type, and the return value is of the time type. The first input parameter is of the date type, the second input parameter is of the text type, and the return value is of the text type. The first input parameter is of the date type, the second input parameter is of the datetime type, and the return value is of the time type. The first input parameter is of the date type, the second input parameter is of the time type, and the return value is of the time type. The first input parameter is of the text type, the second input parameter is of the date type, and the return value is of the text type. The first input parameter is of the text type, the second input parameter is of the text type, and the return value is of the text type. The first input parameter is of the text type, the second input parameter is of the datetime type, and the return value is of the text type. The first input parameter is of the text type, the second input parameter is of the time type, and the return value is of the text type. The first input parameter is of the datetime type, the second input parameter is of the date type, and the return value is of the datetime type. The first input parameter is of the datetime type, the second input parameter is of the text type, and the return value is of the text type. The first input parameter is of the datetime type, the second input parameter is of the datetime type, and the return value is of the datetime type. The first input parameter is of the datetime type, the second input parameter is of the time type, and the return value is of the datetime type. The first input parameter is of the time type, the second input parameter is of the date type, and the return value is of the time type. The first input parameter is of the time type, the second input parameter is of the text type, and the return value is of the text type. The first input parameter is of the time type, the second input parameter is of the datetime type, and the return value is of the time type. The first input parameter is of the time type, the second input parameter is of the time type, and the return value is of the time type.
3	CONVERT_TZ()	Supported	-
4	CURDATE()	Supported	-
5	CURRENT_DATE(), CURRENT_DATE	Supported	-
6	CURRENT_TIME(), CURRENT_TIME	Supported	The time value (after the decimal point) output by precision is rounded off in GaussDB and directly truncated in MySQL. The trailing 0s of the time value (after the decimal point) output by precision are not displayed in GaussDB but displayed in MySQL. GaussDB supports only integer values within the range of [0,6] as the precision of the returned time. For other values, an error is reported. The valid precision value in MySQL is within [0,6], but the input integer value is divided by 256 to get a remainder. For example, if the input integer value is 257, the time value with precision 1 is returned.
7	CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	Supported	The time value (after the decimal point) output by precision is rounded off in GaussDB and directly truncated in MySQL. The trailing 0s of the time value (after the decimal point) output by precision are not displayed in GaussDB but displayed in MySQL. GaussDB supports only the input integer value within the range of [0,6] as the precision of the returned time. If the input integer value is greater than 6, an alarm is generated and the time value is output based on the precision 6. The valid precision value in MySQL is within [0,6], but the input integer value is divided by 256 to get a remainder. For example, if the input integer value is 257, the time value with precision 1 is returned.
8	CURTIME()	Supported	In GaussDB, if a character string or a non-integer value is entered, the value is implicitly converted into an integer and then the precision is verified. If the value is beyond the [0,6] range, an error is reported. If the value is within the range, the time value is output normally. In MySQL, an error is reported. The time value (after the decimal point) output by precision is rounded off in GaussDB and directly truncated in MySQL. The trailing zeros of the time value (after the decimal point) output by precision is not displayed in GaussDB but displayed in MySQL. GaussDB supports only integer values within the range of [0,6] as the precision of the returned time. For other values, an error is reported. The valid precision value in MySQL is within [0,6], but the input integer value is divided by 256 to get a remainder. For example, if the input integer value is 257, the time value with precision 1 is returned.
9	YEARWEEK()	Supported	-
10	DATE_ADD()	Supported	The performance of this function is different from that of MySQL due to interval expression differences. For details, see INTERVAL.
11	DATE_FORMAT()	Supported	-
12	DATE_SUB()	Supported	The performance of this function is different from that of MySQL due to interval expression differences. For details, see INTERVAL.
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	The time value (after the decimal point) output by precision is rounded off in GaussDB and directly truncated in MySQL. The trailing 0s of the time value (after the decimal point) output by precision are not displayed in GaussDB but displayed in MySQL. GaussDB supports only integer values within the range of [0,6] as the precision of the returned time. For other integer values, an error is reported. The valid precision value in MySQL is within [0,6], but the input integer value is divided by 256 to get a remainder. For example, if the input integer value is 257, the time value with precision 1 is returned.
26	LOCALTIMESTAMP, LOCALTIMESTAMP()	Supported	The time value (after the decimal point) output by precision is rounded off in GaussDB and directly truncated in MySQL. The trailing 0s of the time value (after the decimal point) output by precision are not displayed in GaussDB but displayed in MySQL. GaussDB supports only the input integer value within the range of [0,6] as the precision of the returned time. If the input integer value is greater than 6, an alarm is generated and the time value is output based on the precision 6. The valid precision value in MySQL is within [0,6], but the input integer value is divided by 256 to get a remainder. For example, if the input integer value is 257, the time value with precision 1 is returned.
27	MAKEDATE()	Supported	-
28	MAKETIME()	Supported	When the input parameter is NULL, GaussDB does not support self-nesting of the maketime function, but MySQL supports.
29	MICROSECOND()	Supported	-
30	MINUTE()	Supported	-
31	MONTH()	Supported	-
32	MONTHNAME()	Supported	-
33	NOW()	Supported	The time value (after the decimal point) output by precision is rounded off in GaussDB and directly truncated in MySQL. The trailing 0s of the time value (after the decimal point) output by precision are not displayed in GaussDB but displayed in MySQL. GaussDB supports only the input integer value within the range of [0,6] as the precision of the returned time. If the input integer value is greater than 6, an alarm is generated and the time value is output based on the precision 6. The valid precision value in MySQL is within [0,6], but the input integer value is divided by 256 to get a remainder. For example, if the input integer value is 257, the time value with precision 1 is returned.
34	PERIOD_ADD()	Supported	If the input parameter period or result is less than 0, GaussDB reports an error by referring to the performance in MySQL 8.0.x. Integer wrapping occurs in MySQL 5.7. As a result, the calculation result is abnormal.
35	PERIOD_DIFF()	Supported	If the input parameter or result is less than 0, GaussDB reports an error by referring to the performance in MySQL 8.0.x. Integer wrapping occurs in MySQL 5.7. As a result, the calculation result is abnormal.
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	The performance of this function is different from that of MySQL due to interval expression differences. For details, see INTERVAL.
41	SUBTIME()	Supported	MySQL returns NULL if the second input parameter is a string in the DATETIME format. GaussDB can calculate the value. The value range of an input parameter is ['0001-01-01 00:00:00', 9999-12-31 23:59:59.999999]. If the first parameter of the SUBTIME function in MySQL is a dynamic parameter (for example, in a prepared statement), the return type is TIME. Otherwise, the parse type of the function is derived from the parse type of the first parameter. The return value rules of the SUBTIME function in GaussDB are as follows: The first input parameter is of the date type, the second input parameter is of the date type, and the return value is of the time type. The first input parameter is of the date type, the second input parameter is of the text type, and the return value is of the text type. The first input parameter is of the date type, the second input parameter is of the datetime type, and the return value is of the time type. The first input parameter is of the date type, the second input parameter is of the time type, and the return value is of the time type. The first input parameter is of the text type, the second input parameter is of the date type, and the return value is of the text type. The first input parameter is of the text type, the second input parameter is of the text type, and the return value is of the text type. The first input parameter is of the text type, the second input parameter is of the datetime type, and the return value is of the text type. The first input parameter is of the text type, the second input parameter is of the time type, and the return value is of the text type. The first input parameter is of the datetime type, the second input parameter is of the date type, and the return value is of the datetime type. The first input parameter is of the datetime type, the second input parameter is of the text type, and the return value is of the text type. The first input parameter is of the datetime type, the second input parameter is of the datetime type, and the return value is of the datetime type. The first input parameter is of the datetime type, the second input parameter is of the time type, and the return value is of the datetime type. The first input parameter is of the time type, the second input parameter is of the date type, and the return value is of the time type. The first input parameter is of the time type, the second input parameter is of the text type, and the return value is of the text type. The first input parameter is of the time type, the second input parameter is of the datetime type, and the return value is of the time type. The first input parameter is of the time type, the second input parameter is of the time type, and the return value is of the time type.
42	SYSDATE()	Supported	The integer value of the MySQL input parameter is wrapped when reaching the maximum value 255 in one byte. The integer in GaussDB is not wrapped.
43	YEAR()	Supported	-
44	TIME_FORMAT()	Supported	-
45	TIME_TO_SEC()	Supported	-
46	TIMEDIFF()	Supported	-
47	WEEKOFYEAR()	Supported	-
48	TIMESTAMPADD()	Supported	-
49	TIMESTAMPDIFF()	Supported	-
50	TO_DAYS()	Supported	-
51	TO_SECONDS()	Supported	-
52	UNIX_TIMESTAMP()	Supported	Return value difference: In GaussDB, numeric is returned. In MySQL, int is returned.
53	UTC_DATE()	Supported	MySQL supports calling without parentheses, but GaussDB does not. The integer value of the MySQL input parameter is wrapped when reaching the maximum value 255 by one byte. MySQL input parameters support only integers ranging from 0 to 6. GaussDB supports input parameters that can be implicitly converted to integers ranging from 0 to 6.
54	UTC_TIME()	Supported
55	UTC_TIMESTAMP()	Supported
56	WEEK()	Supported	-
57	WEEKDAY()	Supported	-