Magic ETL

Magic ETL

Date_Sub

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

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answers

  • edited February 2024 Answer ✓

    Have you looked at using TIMEDIFF()? Site faviconMySQL TIMEDIFF() Function

    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

    If I solved your problem, please select "yes" above

  • Coach
    Answer ✓

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • edited February 2024 Answer ✓

    Have you looked at using TIMEDIFF()? Site faviconMySQL TIMEDIFF() Function

    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

    If I solved your problem, please select "yes" above

  • Worked great! Thank you

  • Coach
    Answer ✓

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In