Re: rounding to 15 seconds

Hello
  I used ceil() in this exprestion , but you may also use floor() or round().
select to_char(

    to_date('20010101', 'YYYYMMDD')
      +
    ceil((24 * 60 * 60) * (sysdate - to_date('20010101', 'YYYYMMDD') ) / 15) * 15 / (24 * 60 * 60)
  , 'YYYY/MM/DD hh24:mi:ss')
,to_char(sysdate, 'YYYY/MM/DD hh24:mi:ss') from sys.dual;

  The basic idea is to aply the rounding function to the difference between two dates.
  I took 20010101, but it may be any date as well.

  Replacing sysdate with 'quotedate' you should have something like this :

select to_char(

    to_date('2003/09/22', 'YYYY/MM/DD')
      +
    ceil((24 * 60 * 60) * (quotedate - to_date('2003/09/22', 'YYYY/MM/DD') ) / 15) * 15 / (24 * 60 * 60)
  , 'YYYY/MM/DD hh24:mi:ss')
from quote where quotedate > '2003/09/22';

Regards,
 Mihai

"Jake" <me_at_heyjay.com> wrote in message
news:bkn3hr$i3h$1_at_bob.news.rcn.net...
> Hi,
>
> I have a process that retrieves a stock quote, roughly, every 15 seconds.
> I'd like to round the date to every 15 seconds, so that the dates end on
0,
> 15, 30, 45 seconds.
>
> I can't think of a way to do this in sql. I was hoping someone here could
> :)
>
> below is a sample of the data
>
> Thanks
> Jake
>
> SQL> select quotedate from quote where quotedate > '2003/09/22';
>
> QUOTEDATE
> -------------------
> 2003/09/22 08:30:04
> 2003/09/22 08:30:20
> 2003/09/22 08:30:35
> 2003/09/22 08:30:50
> 2003/09/22 08:31:05
> 2003/09/22 08:31:21
> 2003/09/22 08:31:36
> 2003/09/22 08:31:51
> 2003/09/22 08:32:06
> 2003/09/22 08:32:21
> 2003/09/22 08:32:36
>
>
>
Received on Mon Sep 22 2003 - 11:20:03 CDT