How can I convert two timestamps into time format (HH:MM:SS)?

Question

Question

#IRIS Analytics Architect #Java #SQL #InterSystems IRIS

For example, I have two timestamp values ('2024-04-01 10:00:00', '2024-04-01 11:30:30'). I would like to find the difference between these two timestamps, and I need the result in hours:minutes:seconds (hh:mm:ss) format.

Expected Output: 01:30:30

Note: I need an SQL query command. I should not use ClassMethod, Function, or Stored Procedure.

Could anyone please provide me with an SQL query for my question?

Discussion (4)1

Log in or sign up to continue

Vitaliy Serdtsev · Apr 4

select lpad(s\3600,2,0)||':'||lpad(s\60#60,2,0)||':'||lpad((s)#3600#60,2,0) diff
from (select DATEDIFF('s','2024-04-01 09:13:46','2024-04-01 11:11:44') s)

1 0

score 0 · Answer 1 · 2024-04-04T02:23:33-04:00

Robert Cemper · Apr 4

did you check TIMESTAMPDIFF ??

0 0

score 0 · Answer 2 · 2024-04-04T02:29:57-04:00

Yes, Using TIMESTAMPDIFF I can able to reteterive the record Hours Minutes, and Seconds separately. But I am expecting this three combinations Like hours:minutes:seconds

score 0 · Answer 3 · 2024-04-04T07:18:52-04:00

Parameshwaran M... · Apr 4

Thanks @Vitaliy Serdtsev

It's working as expected.

0 0