Rounding hour and minute from time stamp to the closest hour

Database

Rounding hour and minute from time stamp to the closest hour

Hi All, 

I need to round this: 

zc.solved_at_ts (FORMAT 'HH:MI') (CHAR(5)),

to the closest hour. ex if 16:35 then 17:00 and if 16:29 then 16:00

Round function did not work. Any ideas? It needs to be in one line nested something like this: round (zc.solved_at_ts (FORMAT 'HH:MI') (CHAR(5))) 

Thanks so much in advance for the help. 

Ozge

3 REPLIES
N/A

Re: Rounding hour and minute from time stamp to the closest hour

Hi Ozge,

ROUND always results in a DATE (blame Oracle), so you can't use it in that case.

What's the datatype of solved_at_ts and what datatype do you need as result? When you add 30 minutes you could simply extract the hour, e.g. 

EXTRACT(HOUR FROM solved_at_ts + INTERVAL '30' MINUTE)

Re: Rounding hour and minute from time stamp to the closest hour

Thanks a lot Dieter, that will work actually! It was timestamp by the way, forgot to specify. 

Re: Rounding hour and minute from time stamp to the closest hour

Try this:

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
,Converted_Time - TIME '00:00:00' HOUR TO MINUTE AS Rounded_Time;
Khurram