Forum Stats

  • 3,770,497 Users
  • 2,253,126 Discussions
  • 7,875,486 Comments

Discussions

Categories

Rounding Time to nearest 30 minutes

Gary F
Gary F Member Posts: 20
edited Jan 2, 2010 7:03AM in SQL & PL/SQL
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.
· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Dec 28, 2009 12:00PM Accepted Answer
    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.
    · Share on TwitterShare on Facebook
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Dec 28, 2009 11:31AM
    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.
    · Share on TwitterShare on Facebook
  • Gary F
    Gary F Member Posts: 20
    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.
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Dec 28, 2009 12:00PM Accepted Answer
    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.
    · Share on TwitterShare on Facebook
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    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;
    ?
    Hoek
    · Share on TwitterShare on Facebook
  • Gary F
    Gary F Member Posts: 20
    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.
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    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.
    MichaelS
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond 
    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.
    Solomon Yakobson
    · Share on TwitterShare on Facebook
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    edited Dec 28, 2009 11:57AM
    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
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Dec 29, 2009 12:07AM
    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
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond
    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.
    · Share on TwitterShare on Facebook
This discussion has been closed.