Forum Stats

  • 3,839,996 Users
  • 2,262,557 Discussions
  • 7,901,120 Comments

Discussions

Categories

Rounding time to nearest hourly quarter

zebango
zebango Member Posts: 33
edited Jul 6, 2011 10:48AM in SQL & PL/SQL
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
· Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,265 Red Diamond
    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.
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,612 Red Diamond
    edited Mar 9, 2010 8:58PM 
    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.
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
    · Share on TwitterShare on Facebook
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    edited Mar 11, 2010 7:11AM
    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
    · Share on TwitterShare on Facebook
  • frm40735
    frm40735 Member Posts: 603
    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

    ?
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,612 Red Diamond
    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.
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,612 Red Diamond
    edited Mar 11, 2010 7:03AM
    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.
    · Share on TwitterShare on Facebook
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    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
    · Share on TwitterShare on Facebook
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    Solomon Yakobson wrote:
    Now my suggestion:
    Ooh, seconds since midnight! {noformat}*slaps forehead*{noformat} Much better than my second attempt *{:-)
    · Share on TwitterShare on Facebook
  • 873686
    873686 Member Posts: 1
    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
    · Share on TwitterShare on Facebook
This discussion has been closed.