Is the + and - operator defined in standard sql on date/time types, for example Time, Duration, Date, Datetime, Timestamp. Here is an example from MySQL:
select
date '1983-09-05', time '01:02:03', timestamp '2014-01-01 01:02:03',
date '1983-09-05' + time '01:02:03', timestamp '1983-09-05 01:02:03' + time '01:02:03'
It seems the last three results just give gibberish (in terms of actually giving a meaningful result). Is there a standard in how time-types are supposed to add and subtract or it's undefined behavior and it's suggested to use functions for this kind of stuff?
MySQL doesn't produce gibberish. It just gets confused. The problem is that MySQL is trying to figure out what you mean:
+ really for numeric addition or for date/times?MySQL makes choices for these that are (perhaps) counterintuitive. For instance, a date/time values such as '2014-01-01 01:02:03' is converted to a number that looks like 20140101010203. This conversion happens implicitly under some circumstances.
Let me illustrate this just with the dates. You might think these are equivalent:
select '2021-01-01' + 40,
date '2021-01-01' + 40,
'2021-01-01' + interval 40 day
And the results are:
2061 20210141 2021-02-10
What is happening? In the first, + is treated a number addition. the first argument is converted to a number -- that is leading digits up to the first non-digit.
In the second, + is treated a number addition as well. The date is converted to a number and it looks like 20,210,101 -- that is YYYYMMDD as an integer.
Finally, the third tells MySQL to do what you intend -- add 40 days.
This has nothing to do with standard SQL, which clearly defines adding and subtracting intervals and the difference of timestamps. These are just the rules that MySQL uses for disambiguating + and - and for converting date/times to numbers.