Date_Sub

ColinHaze · 2024-02-26T19:49:56+00:00

There was an error rendering this rich post.

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'."

Find more posts tagged with

Accepted answers

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

All comments

ColemenWilson

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

ColinHaze

Worked great! Thank you

ArborRose

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