Date and Time Functions

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

Functions may use time expressions as their input parameters.
Time expressions mainly include text, datetime, date, and time. Besides, all types that can be implicitly converted to time expressions can be 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, time expressions are parsed as the date type. The timestampdiff function parses time expressions 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 of 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 date and datetime ranges, dates beyond the MySQL ranges are still considered 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.

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: When SELECT timestampdiff(hour, '2020-03-01 00:00:00', '2020-02-28 00:00:00+08'); is executed in a MySQL-compatible database, the time function does not automatically calculate the time zone. Therefore, +08 is not identified as the time zone. Instead, + is used as the separator for calculation 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
MySQL	GaussDB	Difference
ADDDATE()	Supported, with differences	The performance of this function is different from that of MySQL due to interval expression differences. For details, see INTERVAL.
ADDTIME()	Supported, with differences	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.
CONVERT_TZ()	Supported.	-
CURDATE()	Supported.	-
CURRENT_DATE(), CURRENT_DATE	Supported.	-
CURRENT_TIME(), CURRENT_TIME	Supported, with differences	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 an integer value within the range of [0,6] as the precision of the returned time. For other values, an error is reported. In MySQL, a precision value within [0,6] is valid, but an 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.
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	Supported, with differences	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 an 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. In MySQL, a precision value within [0,6] is valid, but an 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.
CURTIME()	Supported, with differences	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 0s of the time value (after the decimal point) output by precision are not displayed in GaussDB but displayed in MySQL. GaussDB supports only an integer value within the range of [0,6] as the precision of the returned time. For other values, an error is reported. In MySQL, a precision value within [0,6] is valid, but an 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.
YEARWEEK()	Supported.	-
DATE_ADD()	Supported, with differences	The performance of this function is different from that of MySQL due to interval expression differences. For details, see INTERVAL.
DATE_FORMAT()	Supported.	-
DATE_SUB()	Supported, with differences	The performance of this function is different from that of MySQL due to interval expression differences. For details, see INTERVAL.
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	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 an integer value within the range of [0,6] as the precision of the returned time. For other integer values, an error is reported. In MySQL, a precision value within [0,6] is valid, but an 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.
LOCALTIMESTAMP, LOCALTIMESTAMP()	Supported, with differences	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 an 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. In MySQL, a precision value within [0,6] is valid, but an 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.
MAKEDATE()	Supported.	-
MAKETIME()	Supported, with differences	When the input parameter is NULL, GaussDB does not support self-nesting of the maketime function, but MySQL supports.
MICROSECOND()	Supported.	-
MINUTE()	Supported.	-
MONTH()	Supported.	-
MONTHNAME()	Supported.	-
NOW()	Supported, with differences	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 an 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. In MySQL, a precision value within [0,6] is valid, but an 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.
PERIOD_ADD()	Supported, with differences	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.
PERIOD_DIFF()	Supported, with differences	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.
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, with differences	The performance of this function is different from that of MySQL due to interval expression differences. For details, see INTERVAL.
SUBTIME()	Supported, with differences	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.
SYSDATE()	Supported, with differences	In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value), while GaussDB does not.
YEAR()	Supported.	-
TIME_FORMAT()	Supported.	-
TIME_TO_SEC()	Supported.	-
TIMEDIFF()	Supported.	-
WEEKOFYEAR()	Supported.	-
TIMESTAMPADD()	Supported.	-
TIMESTAMPDIFF()	Supported.	-
TO_DAYS()	Supported.	-
TO_SECONDS()	Supported.	-
UNIX_TIMESTAMP()	Supported, with differences	GaussDB returns values of the numeric type, while MySQL returns values of the int type.
UTC_DATE()	Supported, with differences	MySQL supports calling without parentheses, but GaussDB does not. In MySQL, an integer input value is wrapped when it reaches 255 (maximum value of a one-byte integer value). 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.
UTC_TIME()	Supported, with differences
UTC_TIMESTAMP()	Supported, with differences
WEEK()	Supported.	-
WEEKDAY()	Supported.	-