Archives of the TeradataForum

Message Posted: Fri, 16 Sep 2005 @ 21:44:20 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Rounding time to the nearest quarter hour

From:		Dieter Noeth

Jeff Ohlman wrote:

After searching high-and-low for a quick way to round time to the nearest quarter hour, I have come up with this method. I am posting it here just in case someone else needs to do the same.

This sql converts the time to a fraction (same way Excel does), then divides fraction into 96 (number of quarter hours per day), rounds the number and converts back to standard time.

So 22:08:23 becomes 22:15:00 etc

          > select
          > ((cast((((extract(hour from current_timestamp) / 24.000000 +
          > extract(minute from current_timestamp) / 1440.000000) + (extract(second
          > from current_timestamp) / 86400) )
          > * 96) as decimal(4,0)) / 96.0000000) ) as qtr_hr_dec,
          > (qtr_hr_dec * 24 (int) ) * 10000 + (qtr_hr_dec * 24) mod 1 * 6000
          > (decimal(6,0)) (int) (format '99:99:99')

I didn't test that much, but i think it works, too.

     select
        current_timestamp(2) + interval '7:30' minute to second as x,
        x - extract(second from x) * interval '1' second
          - (extract(minute from x) mod 15) * interval '1' minute

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference