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.
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'.
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.
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)
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 |
|
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 |
|
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 |
|
UTC_TIME() |
Supported, with differences |
|
UTC_TIMESTAMP() |
Supported, with differences |
|
WEEK() |
Supported. |
- |
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