-1

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

shoyeb shahriar
  • 11
  • 4

1 Answers1

0

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.

breadcrumb42
  • 165
  • 1
  • 8
  • No need to recalculate each difference every time, just get the seconds_between and then do division and subtraction with another select statement over the result – astentx Oct 09 '20 at 09:27