Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    6

    Unanswered: Calculating the Hours between two time values in decimal

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try:
    ( MIDNIGHT_SECONDS(T_CLSNG_TIME) - MIDNIGHT_SECONDS(T_OPEN_TIME) ) / 3600.

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

  3. #3
    Join Date
    Sep 2010
    Posts
    6

    Thanks a lot

    Quote Originally Posted by tonkuma View Post
    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

  4. #4
    Join Date
    Sep 2010
    Posts
    6

    Need a help further

    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •