Date_Sub

ColinHaze
ColinHaze Member
in Magic ETL

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

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited February 2024 Answer ✓

    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

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

  • ArborRose
    ArborRose 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

  • ColemenWilson
    ColemenWilson Coach
    edited February 2024 Answer ✓

    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

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

  • ColinHaze
    ColinHaze Member

    Worked great! Thank you

  • ArborRose
    ArborRose 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! **

Categories