How to get difference between 2 timestamp values in SAP Hana?

Question

I have two column timestamps values and would like to find out difference between those times in SAP HANA. I have not found any easiest way to find out like other DB. For better understanding, an example has been given in the following.

COLUMN1: COLUMN2:

Thu Oct 01 2020 09:18:08 GMT+0200 (CEST) Thu Oct 01 2020 15:49:40 GMT+0200 (CEST)

Resulting Column: 06 hours 31 min 32s

Answer 1

You can use the xx_between functions (days_between, seconds_between, nano100_between) and then you can do some math, as in:

CONCAT(CONCAT(CONCAT(CONCAT(CONCAT( CONCAT(to_varchar(to_integer(DAYS_BETWEEN(ED."START_TIME", ED."END_TIME")),'00'), 'D '), -- Days to_varchar(to_integer(NANO100_BETWEEN(Table1."START_TIME", Table1."END_TIME")/10000000/60/60)-DAYS_BETWEEN(Table1."START_TIME", Table1."END_TIME")*24,'00') --Hours),':'), to_varchar(to_integer(((NANO100_BETWEEN(Table1."START_TIME", Table1."END_TIME")/10000000/60/60)-to_integer(NANO100_BETWEEN(Table1."START_TIME", Table1."END_TIME")/10000000/60/60))*60),'00') --Minutes ),':'),to_varchar(to_integer(((((NANO100_BETWEEN(Table1."START_TIME", Table1."END_TIME")/10000000/60/60)-to_integer(NANO100_BETWEEN(Table1."START_TIME", Table1."END_TIME")/10000000/60/60))*60) - to_integer(((NANO100_BETWEEN(Table1."START_TIME", Table1."END_TIME")/10000000/60/60)-to_integer(NANO100_BETWEEN(Table1."START_TIME", Table1."END_TIME")/10000000/60/60))*60))*60), '00'))

The code is written in a way to understand what is going on in terms of time conversion. It could be shortend a lot, if needed.