matrixone
matrixone copied to clipboard
matrixorigin
[Feature Request]: datetime datediff, timediff and timestampdiff function
Is there an existing issue for the same feature request?
- [X] I have checked the existing issues.
Is your feature request related to a problem?
#3589
Describe the feature you'd like
DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); -> 1 mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31'); -> -31
TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type. Returns NULL if expr1 or expr2 is NULL.
The result returned by TIMEDIFF() is limited to the range allowed for TIME values. Alternatively, you can use either of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP(), both of which return integers.
mysql> SELECT TIMEDIFF('2000-01-01 00:00:00', -> '2000-01-01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999'
TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type. Returns NULL if expr1 or expr2 is NULL.
The result returned by TIMEDIFF() is limited to the range allowed for TIME values. Alternatively, you can use either of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP(), both of which return integers.
mysql> SELECT TIMEDIFF('2000-01-01 00:00:00', -> '2000-01-01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999'
Describe implementation you've considered
No response
Documentation, Adoption, Use Case, Migration Strategy
No response
Additional information
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.
This function returns NULL if datetime_expr1 or datetime_expr2 is NULL.
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); -> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); -> 128885
Because time type is the dependency of timediff, this function will be developed after time type completion.
