Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Rounding time to nearest hourly quarter

zebangoMar 9 2010 — edited Jul 6 2011
I have several records like

2/19/2010 1:25:46.000000 PM
2/19/2010 1:40:46.000000 PM
2/19/2010 1:55:46.000000 PM
2/19/2010 2:10:46.000000 PM

I want to round them off to the nearest quarter like
2/19/2010 1:30:00.000000 PM
2/19/2010 1:45:00.000000 PM
2/19/2010 2:00:00.000000 PM
2/19/2010 2:15:00.000000 PM

Any ideas how I would do that? a trunc(col,'mi) only lets me round it to the minute..

Note that records are not necessarily in the 5 minute interval difference it can also be 7 minute difference or 4 minute difference from the nearest quarter...

so I can also have records like
SysUpTime 2/19/2010 10:31:00 PM
SysUpTime 2/19/2010 10:57:00 PM
SysUpTime 2/19/2010 11:31:00 PM

etc.

Edited by: zebango on Mar 9, 2010 4:16 PM

Comments

Frank Kulash
Hi,

If you have a TIMESTAMP column t in table_x, the following will whow each one, and it's rounded-down version: 
SELECT	t
,	TRUNC (t, 'HH') + ( FLOOR ( EXTRACT (MINUTE FROM (t - TRUNC (t, 'HH')))
	      	  	    	  / (60 / 4)
				  )
	      	    	  / 4 
		    	  )
FROM	table_x;
The "magic number" 4 that appear twice in the query is the number of periods per hour.
The rounded value will be a DATE.
Solomon Yakobson 
alter session set nls_date_format = 'dd-MON-yy hh.mi.ss PM'
/
with table_x as (
                 select to_timestamp('2/19/2010 1:25:46.000000 PM','mm/dd/yyyy hh:mi:ss.ff pm') t from dual union all
                 select to_timestamp('2/19/2010 1:40:46.000000 PM','mm/dd/yyyy hh:mi:ss.ff pm') from dual union all
                 select to_timestamp('2/19/2010 1:55:46.000000 PM','mm/dd/yyyy hh:mi:ss.ff pm') from dual union all
                 select to_timestamp('2/19/2010 2:10:46.000000 PM','mm/dd/yyyy hh:mi:ss.ff pm') from dual
                )
SELECT	t,
        trunc(t,'dd') + round(to_char(t,'sssss.ff') / 900) / 96 rounded_t
  FROM  table_x
/

T                                                                           ROUNDED_T
--------------------------------------------------------------------------- ---------------------
19-FEB-10 01.25.46.000000000 PM                                             19-FEB-10 01.30.00 PM
19-FEB-10 01.40.46.000000000 PM                                             19-FEB-10 01.45.00 PM
19-FEB-10 01.55.46.000000000 PM                                             19-FEB-10 02.00.00 PM
19-FEB-10 02.10.46.000000000 PM                                             19-FEB-10 02.15.00 PM

SQL>
900 - number of seconds in 15 minutes
96 number of 15 minute intervals in a day 4 * 24

SY.
Aketi Jyuuzou
I like case expression :D
with table_x as (
 select timestamp '2010-02-19 01:25:46' t from dual union all
 select timestamp '2010-02-19 01:40:46' from dual union all
 select timestamp '2010-02-19 01:55:46' from dual union all
 select timestamp '2010-02-19 02:10:46' from dual)
select t,
case when mod(60*extract(minute from t)+extract(second from t),60*5) > 0
     then t+NumToDsInterVal(60*5-mod(60*extract(minute from t)+extract(second from t),60*5),'SECOND')
     else t end as new2
  from table_x;

T
---------------------------
NEW2
---------------------------
10-02-19 01:25:46.000000000
10-02-19 01:30:00.000000000

10-02-19 01:40:46.000000000
10-02-19 01:45:00.000000000

10-02-19 01:55:46.000000000
10-02-19 02:00:00.000000000

10-02-19 02:10:46.000000000
10-02-19 02:15:00.000000000
Boneist
TRUNC returns a DATE so this could be simplified even further to:
select t,
       trunc(t, 'hh') + floor(to_char(t, 'mi')/15)*15/1440 nearest_15_min
from   table_x
ETA: This rounds down to the nearest 15 minute point, which (because I clearly can't read) is not quite what the OP was after. D'oh!.

Edited by: Boneist on 11-Mar-2010 12:10
frm40735
hi boneist


I tried your sql statement 
select sysdate t,
trunc(sysdate, 'hh') + floor(to_char(sysdate, 'mi')/15)*15/1440 nearest_15_min
from dual
11/03/2010 11:47:12

returned

11/03/2010 11:45:00

when shouldn't it have returned with

11/03/2010 12:00:00

?
Solomon Yakobson
Boneist wrote:
TRUNC returns a DATE so this could be simplified even further to:
But OP asked for "*Rounding* time to nearest hourly quarter". Same applies to Aketi's solution which, BTW, which always bumps it up to next 5 min.

SY.
Solomon Yakobson
DM wrote:
hi boneist


I tried your sql statement
According to OP reqs ("Rounding time to nearest hourly quarter") 11/03/2010 11:47:12 should return 11/03/2010 11:45:00. But 11/03/2010 11:52:30 should return 11/03/2010 12:00:00, while:
SQL> select to_date('11/03/2010 11:52:30','mm/dd/yyyy hh24:mi:ss') t,
  2  trunc(to_date('11/03/2010 11:52:30','mm/dd/yyyy hh24:mi:ss'), 'hh') + floor(to_char(to_date('11/03/2010 11:52:30','mm/dd/yyyy hh
24:mi:ss'),'mi')/15)*15/1440 nearest_15_min
  3  from dual
  4  /

T                     NEAREST_15_MIN
--------------------- ---------------------
03-NOV-10 11.52.30 AM 03-NOV-10 11.45.00 AM

SQL>
Now my suggestion:
SQL> with table_x as (
  2                   select to_date('11/03/2010 11:52:30','mm/dd/yyyy hh24:mi:ss') t from dual
  3                  )
  4  SELECT t,
  5          trunc(t,'dd') + round(to_char(t,'sssss') / 900) / 96 rounded_t
  6    FROM  table_x
  7  /

T                     ROUNDED_T
--------------------- ---------------------
03-NOV-10 11.52.30 AM 03-NOV-10 12.00.00 PM

SQL> with table_x as (
  2                   select to_date('11/03/2010 11:47:12','mm/dd/yyyy hh24:mi:ss') t from dual
  3                  )
  4  SELECT t,
  5          trunc(t,'dd') + round(to_char(t,'sssss') / 900) / 96 rounded_t
  6    FROM  table_x
  7  /

T                     ROUNDED_T
--------------------- ---------------------
03-NOV-10 11.47.12 AM 03-NOV-10 11.45.00 AM

SQL>
SY.
Boneist
Solomon Yakobson wrote:
But OP asked for "*Rounding* time to nearest hourly quarter". Same applies to Aketi's solution which, BTW, which always bumps it up to next 5 min.
In which case, just substitute "Round" for "Floor"!

Although thinking about it, you'd probably want to add seconds into the mix:
with table_x as (select to_timestamp('11/03/2010 11:45:39', 'dd/mm/yyyy hh24:mi:ss') t from dual union all
                 select to_timestamp('11/03/2010 11:52:29', 'dd/mm/yyyy hh24:mi:ss') t from dual union all
                 select to_timestamp('11/03/2010 11:52:30', 'dd/mm/yyyy hh24:mi:ss') t from dual union all
                 select to_timestamp('11/03/2010 11:52:31', 'dd/mm/yyyy hh24:mi:ss') t from dual union all
                 select to_timestamp('11/03/2010 12:00:01', 'dd/mm/yyyy hh24:mi:ss') t from dual union all
                 select to_timestamp('11/03/2010 12:07:29', 'dd/mm/yyyy hh24:mi:ss') t from dual union all
                 select to_timestamp('11/03/2010 12:07:31', 'dd/mm/yyyy hh24:mi:ss') t from dual union all
                 select to_timestamp('11/03/2010 13:22:10', 'dd/mm/yyyy hh24:mi:ss') t from dual union all
                 select to_timestamp('11/03/2010 13:22:40', 'dd/mm/yyyy hh24:mi:ss') t from dual)
select t,
       trunc(t, 'hh') + ROUND((to_char(t, 'mi') + (to_char(t, 'ss')/60))/15)*15/1440 nearest_15_min
from   table_x

T                                NEAREST_15_MIN       
-------------------------------- ---------------------
11/03/2010 11:45:39.000000       11/03/2010 11:45:00  
11/03/2010 11:52:29.000000       11/03/2010 11:45:00  
11/03/2010 11:52:30.000000       11/03/2010 12:00:00  
11/03/2010 11:52:31.000000       11/03/2010 12:00:00  
11/03/2010 12:00:01.000000       11/03/2010 12:00:00  
11/03/2010 12:07:29.000000       11/03/2010 12:00:00  
11/03/2010 12:07:31.000000       11/03/2010 12:15:00  
11/03/2010 13:22:10.000000       11/03/2010 13:15:00  
11/03/2010 13:22:40.000000       11/03/2010 13:30:00
Boneist
Solomon Yakobson wrote:
Now my suggestion:
Ooh, seconds since midnight! {noformat}*slaps forehead*{noformat} Much better than my second attempt *{:-)
873686
with table_x as (
select to_timestamp('2/19/2010 1:25:46 PM','mm/dd/yyyy hh:mi:ss pm') t from dual union all
select to_timestamp('2/19/2010 1:40:46 PM','mm/dd/yyyy hh:mi:ss pm') from dual union all
select to_timestamp('2/19/2010 1:55:46 PM','mm/dd/yyyy hh:mi:ss pm') from dual union all
select to_timestamp('2/19/2010 2:06:15 PM','mm/dd/yyyy hh:mi:ss pm') from dual
)
SELECT t,
trunc(t,'dd') + ROUND (to_char(t,'sssss') / 300) / 288 rounded_t
FROM table_x
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 3 2011
Added on Mar 9 2010
10 comments
32,085 views