There is a difference between explicit and implicit DATETIME->TIME conversion when using a datetime with zero YYYYMM and a non-zero day:
Notice, implicit conversion adds days to hours, while explicit does not.
The problem also presents in the upstream.
The same problem is repeatable when casting a DATETIME column to TIME.
Note, if I now ALTER the DATETIME column to TIME, it does mix days to hours:
This is inconsistent. Conversion should work the same way in all cases:
- Explicit CAST
- Implicit conversion on INSERT and SET
- Explicit conversion on ALTER.
In 10.3 (as of 2018-02-07) implicit conversion from DATETIME to TIME at INSERT time and explicit conversion at ALTER time work as follows:
- if the YYYYMM part is zero, then the data represents a "INTERVAL DAY TO SECOND"-alike value, then mix days to hours
- If the YYYYMM part is not zero, then the data represents a real DATETIME value, then truncate YYYYMMDD and preserve the hhmmss part only.
There are two options:
1. Make CAST work like INSERT/ALTER (mix days to hours if YYYYMM is zero)
2. Make INSERT/ALTER work like CAST (truncate the YYYYMMDD part)
Sergei is included towards #2.
Bar would also prefer #2, but is afraid that some users can have upgrade problems.