MySql TIMESTAMPDIFF with float value-CodePudding

hoping you all are doing well, I'm working with MySql and using a TIMESTAMPDIFF function like this:

TIMESTAMPDIFF(HOUR, 07:00:00, 16:30:00) and this return 9

So my question is, there's a way to get the return as 9.5 (nine and a half hours)?

Thanks!

I've already tried using TIMEDIFF and doing the substract but it returns 9.3

CodePudding user response：

I'd say you can use the following query:

SELECT TIME_TO_SEC('16:30:00')/3600 - TIME_TO_SEC('07:00:00')/3600 as hours_difference;

This will give you the result 9.5.

Alternatively, you can use the TIMEDIFF() function to get the time difference between two time values in the format 'HH:MM:SS':

SELECT TIMEDIFF('16:30:00', '07:00:00') as hours_difference;

This will give you the result '09:30:00' which you can then convert to decimal by doing :

SELECT (TIME_TO_SEC(TIMEDIFF('16:30:00', '07:00:00'))/3600) as hours_difference;

This will give you the result 9.5.

Let me know if it works :)

CodePudding user response：

The TIMESTAMPDIFF function in MySQL can be used to find the difference between two timestamp values in a specified unit (e.g. days, hours, minutes, etc.). The function returns an integer value, not a float.

The syntax for using TIMESTAMPDIFF is as follows:

TIMESTAMPDIFF(unit, timestamp1, timestamp2)

where "unit" is the unit of measurement you want to use (e.g. DAY, HOUR, MINUTE, etc.), "timestamp1" is the first timestamp, and "timestamp2" is the second timestamp.

For example, to find the number of days between "2022-01-01" and "2022-01-15":

SELECT TIMESTAMPDIFF(DAY, '2022-01-01', '2022-01-15'); This will return 14

If you want to get a fractional value you can use DATEDIFF instead and divide the result by the number of days in your unit.

SELECT DATEDIFF('2022-01-15','2022-01-01')/365.25; This will return 0.37945205479452