Oracle 23 introduced CEIL, FLOOR for datetime data types (including interval), and extended ROUND and TRUNC to intervals.
It also kept what I view as a flawed choice, of returning DATE data type for inputs of type TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. (Flawed because one might want to round or truncate a timestamp with time zone, for example, to a precision of hours or days, but preserve the time zone information.)
What puzzles me, even with this choice (return DATE from all TIMEZONE types) is the choice not to allow seconds precision. Why not?
I know that even in the older versions of Oracle, where we only had ROUND and TRUNC, we couldn't give ‘SS’ as precision specifier. Not sure why not; but if Oracle made the effort to add CEIL and FLOOR, and extend them to intervals, why didn't they take the opportunity to support the ‘SS’ precision at the same time?
In a sense, the ability does exist for TRUNC (so clearly Oracle has already done the coding). If we CAST a timestamp (of any kind) to DATE, or if we insert a timestamp (of any kind) into a DATE column, or if we add 0 to a timestamp (0 as in “0 days”, causing an implicit cast of the timestamp to a date), the result is always a TRUNC of the timestamp to ‘SS’ precision. So that does exist, as an implicit operation only. Why not make it explicit - and then why not extend it to the other functions (and to intervals)? Of course, for DATE and TIMESTAMP (but not for INTERVAL), FLOOR is the same as TRUNC for A.D. inputs, so we can work around it, but not so for ROUND and CEIL.
For finer time scales, it would make sense to be able to also round timestamps and intervals to fractions of a second (to milliseconds or microseconds etc.) Of course, for timestamp data types that ship has sailed, since ROUND and TRUNC return date data type; they would need to return timestamp for what I am proposing, and that would break existing code. (Which is a red herring in my opinion; the functions could have an overload with a third argument for the desired return data type, with default ‘DATE’ - that would not break existing code.) But for intervals, where it may really make the most sense, it would indeed be helpful to be able to round to submultiples of a second. Perhaps this could be considered if the ‘SS’ precision is supported in a future version.
Does anyone know why Oracle left out the “seconds” precision for the rounding functions?