All Forums Database
M.Saeed Khurram 544 posts Joined 09/12
30 Nov 2013
Conversion of Time to INTERVAL

Hi,
I was working on a scenario and build the following logic, I need to convert the time output from this case statement to INTERVAL HOUR TO MINUTE.

SELECT   CURRENT_TIME(0), 
CASE WHEN EXTRACT(MINUTE FROM CURRENT_TIME(0)) - 30   >= 0 
                    THEN CURRENT_TIME(0) + ((60 - EXTRACT(MINUTE FROM CURRENT_TIME(0))) * INTERVAL  '1' MINUTE)
               ELSE CURRENT_TIME(0) -  EXTRACT(MINUTE FROM CURRENT_TIME(0))  * INTERVAL  '1' MINUTE 
END  AS Converted_Time;

Thanks,
 

Khurram

dnoeth 4628 posts Joined 11/04
01 Dec 2013

Hi Khurram,
TIME -TIME results in an interval:

Converted_Time - time '00:00:00' hour to second(0)

 

Dieter

M.Saeed Khurram 544 posts Joined 09/12
01 Dec 2013

Dieter,
Yes the TIME -TIME is an interval, But is there any possiblity to convert the result of above query to HH:MI format?
 

Khurram

dnoeth 4628 posts Joined 11/04
01 Dec 2013

Hi Khurram,
simply change the resulting type:

Converted_Time - time '00:00:00' hour to minute

Dieter

M.Saeed Khurram 544 posts Joined 09/12
01 Dec 2013

Wow, thats great. I was unable to think that way. 
Thank you!
 

Khurram

You must sign in to leave a comment.