Oracle – Round time to nearest x minutes

30 minutes = 24 * 2
10 minutes = 24 * 6
5 minutes = 24 * 12

>select to_char(sysdate, ‘HH24:MI’),to_char(sysdate + 1/(24*6) – mod(sysdate – trunc(sysdate),1/(24*6)), ‘HH24:MI’) from dual;

select to_char(CAST(SYSTIMESTAMP AS DATE), ‘HH24:MI’),to_char(CAST(SYSTIMESTAMP AS DATE) + 1/(24*12) – mod(CAST(SYSTIMESTAMP AS DATE) – trunc(CAST(SYSTIMESTAMP AS DATE)),1/(24*12)), ‘HH24:MI’) from dual;

Thanks to Solomon Yakobson
https://forums.oracle.com/forums/thread.jspa?threadID=1006150
http://download.oracle.com/docs/cd/B19306_01/olap.102/b14346/dml_x_reserved007.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s