Date_Sub

ColinHaze

Hi,

I have two dates that are timestamps ex(dec 12, 2023 6:30:00 Am) I am trying to subtract the two dates to figure out how long a certain task took to complete. I am using date_sub and I am receiving this error, how would I fix this?
"Calculation Error : Operator '-' cannot be applied to type 'Timestamp'."

ColemenWilson

Have you looked at using TIMEDIFF()? https://www.w3schools.com/sql/func_mysql_timediff.asp

Here is what I use to calculate duration between two datetime:
CASE WHEN ((UNIX_TIMESTAMP(`datetime`) - UNIX_TIMESTAMP((`datetime2`)))/3600) < 24
THEN TIMEDIFF(`datetime`,`datetime2`)
ELSE
(UNIX_TIMESTAMP(`datetime` - UNIX_TIMESTAMP((`datetime2`)))/3600)
END

ArborRose

Hours:
DATEDIFF(timestamp2, timestamp1) * 24
Minutes:
DATEDIFF(timestamp2, timestamp1) * 24 * 60
Seconds:
DATEDIFF(timestamp2, timestamp1) * 24 * 60 * 60

ColemenWilson

Have you looked at using TIMEDIFF()? https://www.w3schools.com/sql/func_mysql_timediff.asp

Here is what I use to calculate duration between two datetime:
CASE WHEN ((UNIX_TIMESTAMP(`datetime`) - UNIX_TIMESTAMP((`datetime2`)))/3600) < 24
THEN TIMEDIFF(`datetime`,`datetime2`)
ELSE
(UNIX_TIMESTAMP(`datetime` - UNIX_TIMESTAMP((`datetime2`)))/3600)
END

ColinHaze

Worked great! Thank you

ArborRose

Hours:
DATEDIFF(timestamp2, timestamp1) * 24
Minutes:
DATEDIFF(timestamp2, timestamp1) * 24 * 60
Seconds:
DATEDIFF(timestamp2, timestamp1) * 24 * 60 * 60