Steve Steve - 1 year ago 117
SQL Question

Timestamp conversion to date, time and duration calculation

I have scheduled tasks table where I store scheduled task id, type, exec_time, end_time etc...

I get the exec/end time as timestamp.

For BI purposes I need to somehow calculate the duration (end_time - exec_time) and return it in hh:mm:ss.

The format of the exec/end_time is dd/mm/yy hh:mm:ss:miliseconds o'clock.

Please help me with this.
Thanks in advance!

Answer Source

You can do this is in Tableau using a calculated field. This may not be as elegant as what you could do in the DBMS; however, it will work:

RIGHT("0" + STR(INT(DATEDIFF("second",[Exec Time],[End Time]) / 60)),2)
+ ":" +
RIGHT("0" + STR(INT((DATEDIFF("second",[Exec Time],[End Time]) % 6000) / 60)),2)
+ ":" +
RIGHT("0" + STR(INT((DATEDIFF("second",[Exec Time],[End Time]) % 600))),2)

"Duration" is the calculated field in my screenshot below:

enter image description here

UPDATE - per additional comment -

To make it a time measure:

   INT(DATEDIFF("second",[Exec Time],[End Time]) / 60),
   INT((DATEDIFF("second",[Exec Time],[End Time]) % 6000) / 60),
   INT((DATEDIFF("second",[Exec Time],[End Time]) % 600))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download