MySQL – Generated Columns with Non-Deterministic Functions

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

The result of the query seems to be correct and the server executes it quickly.

But the root node of the execution plan has a warning:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice

What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?

The warning is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.

The warning is triggered by the implicit conversion used for the collation change. If using the collation is the easiest way to get correct results, feel free to leave it as it is. Alternatively, if you explain more about why it is needed, someone will advise you.

As an aside, the REPLACE could be replaced with:

REPLICATE(N'[0-9A-F]', 8);

(This answer is a summary of the comments to the question.)