Forum Stats

  • 3,838,243 Users
  • 2,262,343 Discussions
  • 7,900,552 Comments

Discussions

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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,227 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.
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,586 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.
  • 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
  • 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
  • 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

    ?
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,586 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.
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,586 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.
  • 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  
  • 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 *{:-)
  • 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
This discussion has been closed.