Date and Time Functions
![](https://support.huaweicloud.com/intl/en-us/productdesc-gaussdb/public_sys-resources/note_3.0-en-us.png)
The following describes the date and time function compatibility between GaussDB and MySQL:
- In the 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.
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.
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 date result after calculation is within the range [0000-01-01,0001-01-01], the result is returned normally. MySQL returns '0000-00-00'.
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.
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.
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 (:).
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)
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. |
|
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 last 0 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 modulated by 256. 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 last 0 of the time value (after the decimal point) output by precision is 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 modulated by 256. 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 last 0 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 modulated by 256. 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 last 0 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 integer values, an error is reported. The valid precision value in MySQL is within [0,6], but the input integer value is modulated by 256. 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 last 0 of the time value (after the decimal point) output by precision is 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 modulated by 256. 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 last 0 of the time value (after the decimal point) output by precision is 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 modulated by 256. 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 and date are 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. |
|
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 in 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. |
- |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot