12 Replies Latest reply on Jan 2, 2010 12:03 PM by Solomon Yakobson

    Rounding Time to nearest 30 minutes

    Gary F
      Good day,

      I'm looking for a way to round the time portion of a date value. My query needs to return the time of day, rounded to the nearest 30 minutes - top of the hour or bottom of the hour.

      If sysdate is: 12/28/2009 11:08:19 AM
      I need to see: 12/28/2009 11:00:00 AM

      If sysdate returns: 12/28/2009 11:18:19 AM
      I need to see: 12/28/2009 11:30:00 AM

      Any help is appreciated.

      Regards,

      Gary F.
        • 1. Re: Rounding Time to nearest 30 minutes
          Frank Kulash
          Hi, Gary,
          SELECT     SYSDATE
          ,     TRUNC (SYSDATE) + ( ROUND ( (SYSDATE - TRUNC (SYSDATE))
                                          * 48
                                )
                            / 48
                            )
          FROM    dual;
          Edited by: Frank Kulash on Dec 28, 2009 11:18 AM
          The original answer was for a different question , as OP noted.
          • 2. Re: Rounding Time to nearest 30 minutes
            Gary F
            Frank,

            Thanks for the quick response. I do appreciate it.

            However, I need to round to the nearest half hour. At 1 minute past the hour I need to go back one minute to the top of the hour. At 25 past the hour, I need to go forward 5 minutes to the bottom of the hour.

            Thanks,

            Gary F.
            • 3. Re: Rounding Time to nearest 30 minutes
              Frank Kulash
              Hi,

              Sorry, I mis-read your message.

              For rounding to a half-hour (or any inteval that TRUNC does not handle) you can figure out the number of such periods since midnight (or some other convenient base point), round that number, and then add that many periods to midnight, like this:
              ALTER SESSION     SET NLS_DATE_FORMAT = 'DD-Mon-YYYY HH24:MI:SS';
              
              SELECT     SYSDATE
              ,     TRUNC (SYSDATE) + ( ROUND ( (SYSDATE - TRUNC (SYSDATE))
                                              * 48
                                    )
                                / 48
                                )
              FROM    dual;
              In this case, there are 48 periods in a day.
              Multiplying by 48 converts days to periods, dividing by 48 converts periods to days.
              • 4. Re: Rounding Time to nearest 30 minutes
                Hoek
                Does this work for you:
                with dates as ( -- generating sample data
                select to_date('12/28/2009 11:08:19 AM', 'mm/dd/yyyy hh:mi:ss AM') dt from dual union all
                select to_date('12/28/2009 11:18:19 AM', 'mm/dd/yyyy hh:mi:ss AM') from dual
                )
                --
                -- actual query
                --
                select to_char(dt, 'mm/dd/yyyy hh:')
                       ||
                       to_char(round(to_number(to_char(dt, 'mi'))/30)*30, 'fm00')
                       ||
                       ':00'
                from   dates;
                ?
                1 person found this helpful
                • 5. Re: Rounding Time to nearest 30 minutes
                  Gary F
                  Frank & Hoek,

                  Thanks to both of you for helping me solve this simple query.

                  I may use either or both of your solutions.

                  Regards,

                  Gary F.
                  • 6. Re: Rounding Time to nearest 30 minutes
                    MichaelS
                    Or
                    SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss'
                    /
                    Session altered.
                    
                    SQL> with dates as ( 
                       select to_date('12/28/2009 11:14:59 AM', 'mm/dd/yyyy hh:mi:ss AM') dt from dual union all
                       select to_date('12/28/2009 11:15:00 AM', 'mm/dd/yyyy hh:mi:ss AM') from dual
                    )
                    --
                    --
                    select dt, trunc(dt) + round(to_char(dt,'sssss')/1800)/48 from dates
                    /
                    DT                    TRUNC(DT)+ROUND(TO_CHAR(DT,'SSSSS')/1800)/48
                    --------------------- --------------------------------------------
                    28/12/2009 11:14:59   28/12/2009 11:00:00                         
                    28/12/2009 11:15:00   28/12/2009 11:30:00                         
                    
                    2 rows selected.
                    1 person found this helpful
                    • 7. Re: Rounding Time to nearest 30 minutes
                      Solomon Yakobson
                      your_date + 1/48 - mod(your_date - trunc(your_date),1/48)
                      For eaxmple:
                      SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY HH24:MI:SS'
                        2  /
                      
                      Session altered.
                      
                      SQL> select sysdate dt,sysdate + 1/48 - mod(sysdate - trunc(sysdate),1/48) dt_rounded_to_half_hour
                        2    from dual
                        3  /
                      
                      DT                   DT_ROUNDED_TO_HALF_H
                      -------------------- --------------------
                      28-Dec-2009 11:53:12 28-Dec-2009 12:00:00
                      
                      SQL> 
                      SY.
                      1 person found this helpful
                      • 8. Re: Rounding Time to nearest 30 minutes
                        Hoek
                        Hi Gary,
                        I may use either or both of your solutions.
                        But from an 'elegance point of view', I suggest you should use Frank's or Michael's solution ;)

                        edit
                        Add Solomon to the above as well.

                        Edited by: hoek on Dec 28, 2009 5:56 PM
                        • 9. Re: Rounding Time to nearest 30 minutes
                          Aketi Jyuuzou
                          I like case expression B-)
                          ALTER SESSION   SET NLS_DATE_FORMAT = 'YYYY-mm-DD HH24:MI:SS';
                          
                          with dates as ( -- generating sample data
                          select to_date('12/28/2009 11:08:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
                          select to_date('12/28/2009 11:18:19', 'mm/dd/yyyy hh:mi:ss') from dual)
                          select dt,
                          case when to_char(dt,'mi') between '00' and '14'
                               then trunc(dt,'HH24')
                               when to_char(dt,'mi') between '15' and '44'
                               then trunc(dt,'HH24') + interVal '30' minute
                               when to_char(dt,'mi') between '45' and '59'
                               then trunc(dt,'HH24') + interVal '60' minute
                               end as new
                            from dates;
                          
                          DT                   NEW
                          -------------------  -------------------
                          2009-12-28 11:08:19  2009-12-28 11:00:00
                          2009-12-28 11:18:19  2009-12-28 11:30:00
                          • 10. Re: Rounding Time to nearest 30 minutes
                            Solomon Yakobson
                            Aketi Jyuuzou wrote:
                            I like case expression B-)
                            Me too, but I like then simple B-) :
                            with dates as ( -- generating sample data
                            select to_date('12/28/2009 11:08:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
                            select to_date('12/28/2009 11:18:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
                            select to_date('12/28/2009 11:38:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
                            select to_date('12/28/2009 11:48:19', 'mm/dd/yyyy hh:mi:ss') from dual)
                            select dt,
                            case when to_char(dt,'mi') >= '45'
                                 then trunc(dt,'hh') + interVal '60' minute
                                 when to_char(dt,'mi') >= '15'
                                 then trunc(dt,'hh') + interVal '30' minute
                                 else trunc(dt,'hh')
                                 end as new
                              from dates
                            /
                            
                            DT                  NEW
                            ------------------- -------------------
                            2009-12-28 11:08:19 2009-12-28 11:00:00
                            2009-12-28 11:18:19 2009-12-28 11:30:00
                            2009-12-28 11:38:19 2009-12-28 11:30:00
                            2009-12-28 11:48:19 2009-12-28 12:00:00
                            
                            SQL> 
                            SY.
                            • 11. Re: Rounding Time to nearest 30 minutes
                              Aketi Jyuuzou
                              That is nice one !!
                              I arranged mine to simpler :-)
                              ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';
                              
                              with dates as ( -- generating sample data
                              select to_date('12/28/2009 11:08:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
                              select to_date('12/28/2009 11:18:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
                              select to_date('12/28/2009 11:38:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
                              select to_date('12/28/2009 11:48:19', 'mm/dd/yyyy hh:mi:ss') from dual)
                              select dt,
                              case when to_char(dt,'mi') between '15' and '44'
                                   then trunc(dt,'hh24') + interVal '30' minute
                                   else round(dt,'hh24') end as new
                              from dates;
                              
                              DT                   NEW
                              -------------------  -------------------
                              2009-12-28 11:08:19  2009-12-28 11:00:00
                              2009-12-28 11:18:19  2009-12-28 11:30:00
                              2009-12-28 11:38:19  2009-12-28 11:30:00
                              2009-12-28 11:48:19  2009-12-28 12:00:00
                              • 12. Re: Rounding Time to nearest 30 minutes
                                Solomon Yakobson
                                Aketi Jyuuzou wrote:

                                I arranged mine to simpler :-)
                                Nice.

                                SY.