cancel
Showing results for 
Search instead for 
Did you mean: 

How to Calculate the difference between timestamps in HANA ?

Hello,

Can some one help me with the syntax to calculate the difference between two time stamps in HH:MM

For example start time : 2020-02-27 17:57:44.0 , End time : 2020-02-27 19:23:43.22

Also i am creating another calculated column to populate the date from time stamps, which function i can use to take the date from timestamp?

Thank you.

Brendon

Accepted Solutions (1)

Accepted Solutions (1)

KonradZaleski
Active Contributor

For calculating difference between two timestamps you can use following syntax (SQL Engine😞

floor((seconds_between('2020-02-27 17:57:44.0','2020-02-27 19:23:43.22')/3600) )
|| ':' ||
floor( mod( (seconds_between('2020-02-27 17:57:44.0','2020-02-27 19:23:43.22')) , 3600) / 60 ) 

To get the date from timestamp you can simply use

TO_DATE('2020-02-27 17:57:44.0')

Results will be as follows:

0 Kudos

Hello Konrad,

Thanks for the help.
I have created the calculated columns using the provided scripts but the difference is not showing the expected values
Calculated columns
1. RUNTIME = floor((seconds_between("TEST_START","TEST_END")/3600) )
Data Type = seconddat

2. RUNTIME_2 = floor( mod( (seconds_between("TEST_START","TEST_END")) , 3600) / 60 )
DATA Type = seconddate

I have attached the output. Could you please provide your inputs.

Thank you

Brendon

KonradZaleski
Active Contributor

But the output of the calculation should be of VARCHAR data type instead of SECONDDATE.
The value which you want to display [HH:MM] is a string and not a date.

Please change data type and you should get desired result.

0 Kudos

Thanks Konrad,
It works now, really helpful.

KonradZaleski
Active Contributor
0 Kudos

Great, I'm happy that I could help.

Answers (0)