on 10-07-2020 1:44 PM
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
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.