Hana hour and minutes calculation

[former_member230931]

Hi All,

I have a requirement to calculate time deduction calculation between 2 time fields defined as HH24:MIN format,

for example this is the calculation that should happen;

A-B = C

10:30 -02:30 = 08:30

What the formula for this? Should I use ADD_SECOND formula somehow

Thanks

[muthuram_shanmugavel2 · Accepted solution]

Hi Dick,

You can use SECONDS_BETWEEN to calculate Hours and Minutes difference between two time fields.

Example:

SELECT SECONDS_BETWEEN ('02:30', '10:30') / 3600

As "HOURS_BETWEEN" FROM DUMMY

SELECT SECONDS_BETWEEN ('02:30', '10:30') / 60

As "MINUTES_BETWEEN" FROM DUMMY

SELECT SECONDS_BETWEEN ('10:30', '14:30') / 60

As "MINUTES_BETWEEN" FROM DUMMY

Regards,

Muthuram

[former_member230931 · Accepted solution]

close but not quite

SECONDS_BETWEEN ('10:30', '14:45') / 3600

"HOURS_BETWEEN" FROM DUMMY

give 4.25 as the answer so still you need to convert the results

[muthuram_shanmugavel2 · Accepted solution]

This Query will give the required output:

SELECT

To_Int(SECONDS_BETWEEN ('10:30', '15:15') / 3600) || ' Hours ' ||

Mod ((SECONDS_BETWEEN ('10:30', '15:15') / 60),60)  || ' Minutes '

As "Hours_BETWEEN"

FROM DUMMY

If you want the results in 4:45 format,

Please change the concatenation in SQL.

Regards,

Muthuram

[former_member216276 · Accepted solution]

This message was moderated.

[former_member4938]

Hi Dick Smiths,

There is a work around, that will do what you need.

• Split Hours and Minutes from the fields
• Calculate absolute difference using that split up

Ex :

        let us consider two time fields A and B in the format HH:MM (24 Hrs), then use this formula to find the difference between two fields.

STRING(ABS (HOUR(A) - HOUR(B))) + ':' + STRING(ABS (MINUTE(A) -MINUTE(B)))

This syntax will work in calculated columns of calculation view in hana.

If your scenario is different than this, let me know,

Hope this helps you,

Regards,

HarikrishnanS