Conversion of Time to INTERVAL

Database
Enthusiast

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
4 REPLIES
Senior Apprentice

Re: Conversion of Time to INTERVAL

Hi Khurram,

TIME -TIME results in an interval:

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

Re: Conversion of Time to INTERVAL

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
Senior Apprentice

Re: Conversion of Time to INTERVAL

Hi Khurram,

simply change the resulting type:

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

Enthusiast

Re: Conversion of Time to INTERVAL

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

Thank you!

Khurram