Calculating the Hours between two time values in decimal

dinesh1985 · #1 (**permalink**) 09-24-10, 08:26

Dear Folks,
I want to calculate the elapsed hours between two time columns. I am using timestampdiff method for the same. I am able to get the value. But facing an issue of decimal values. For example the elapsed hours between 09:00:00 and 20:30:00 is coming as 11 instead of 11.5. I am using below syntax to get the output.

Code:

select
T_OPEN_TIME,
T_CLSNG_TIME,
TIMESTAMPDIFF
(
8,
char
(
TIMESTAMP(CURRENT date,T_CLSNG_TIME)
-
TIMESTAMP(CURRENT date,T_OPEN_TIME)
)
)
as elapsed_hours
from HOURS_TABLE

Please help me with this

tonkuma · #2 (**permalink**) 09-24-10, 08:54

Please try:
( MIDNIGHT_SECONDS(T_CLSNG_TIME) - MIDNIGHT_SECONDS(T_OPEN_TIME) ) / 3600.

Note: don't forget decimal point(".").

dinesh1985 · #3 (**permalink**) 09-24-10, 09:57

Quote:

Originally Posted by tonkuma

Please try:
( MIDNIGHT_SECONDS(T_CLSNG_TIME) - MIDNIGHT_SECONDS(T_OPEN_TIME) ) / 3600.

Note: don't forget decimal point(".").

Thanks a lot ..its working perfectly

dinesh1985 · #4 (**permalink**) 10-04-10, 08:17

Hi tonkuma,
I am using the below query and I am getting the proper result.

Code:

select
T_OPEN_TIME,
T_CLSNG_TIME,
TIMESTAMPDIFF
(
4,
char
(
TIMESTAMP(CURRENT date,T_CLSNG_TIME)
-
TIMESTAMP(CURRENT date,T_OPEN_TIME)
)/60.
)
as elapsed_hours
from HOURS_TABLE

but when I am exporting the data to a file, I am getting the values
+9.50000000000000000000 instead of 9.5.When I tried to cast as decimal(2,1),I am getting stack overflow error. Can you please give me some help on this

tonkuma · #5 (**permalink**) 10-05-10, 01:01

Quote:

but when I am exporting the data to a file, I am getting the values
+9.50000000000000000000 instead of 9.5.

I don't know exactly the functionality of EXPORT command.

Quote:

When I tried to cast as decimal(2,1),I am getting stack overflow error.

Please study the meaning of first number(n) and second number(m) in data type DEC(n , m).

Why do you want to use TIMESTAMPDIFF function?
It's get longer than my example using MIDNIGHT_SECONDS functions.

Internet Services

Web Development

Digital Marketing

Consumer Tech