Timestamp Calculation giving incorrect answer?

Hi there,

Bare with me as I am still new to Klipfolio. I am trying to calculate the difference between 2 timestamps both in the same format (yyyy-MM-dd hh:mm:ss). The difference when calculated manually should all be in minutes, maybe a few that go hours, but all results that I'm getting when I simply subtract the end time stamp from the beginning timestamp are incorrect. Looking for a little insight! Thanks in advance.

1 comment

  • Avatar
    Parker Selman Official comment

    Hi Brian,

    Thanks for the post! 

    In order to properly subtract time values, you'll need to first convert your timestamps to Unix format using the DATE function, and then run your calculation. This will return the amount of seconds between the two timestamps. From there, you can use the DATE_CONVERT function to convert this seconds value into hh:mm format. 

    Here's what this full formula would roughly look like in your case:

    DATE_CONVERT(DATE(@1st timestamp,"yyyy-MM-dd hh:mm:ss") - DATE(@2nd timestamp, "yyyy-MM-dd hh:mm:ss") , "ss" , "hh:mm") 

    I hope this helps! If you still require support for this, please send your question over to support@klipfolio.com and our support team can assist further! 


Please sign in to leave a comment.