Thanks Kenneth, playing with it now
Jake
<Kenneth Koenraadt> wrote in message
news:3f6f4a35.4358046_at_news.inet.tele.dk...
> Ooopss...in the 2nd SQL, the addition came too late, it's
>
> select quotedate,trunc(quotedate,'MI') +
> trunc((to_number(to_char(quotedate,'SS'))+15)/15)*15/60/60/24 from
> quote where quotedate > '2003/09/22';
>
>
> Which unfortunately rounds 14:10:00 to 14:10:15, which is probably not
> what you want, so instead :
>
> select quotedate,trunc(quotedate,'MI') +
> trunc((to_number(to_char(quotedate,'SS')) +
>
> decode(mod(to_number(to_char(quotedate,'SS')),15),0,0,15))/15)*15/60/60/24
> from quote where quotedate > '2003/09/22';
>
> Does the trick.
>
>
> - Kenneth Koenraadt
>
>
>
>
> On Mon, 22 Sep 2003 18:43:54 GMT, Kenneth Koenraadt wrote:
>
> >Hi Jake,
> >
> >There's a lot of ways to do it.
> >
> >Mihai has already shown you one. Here's another (short) one :
> >
> > select trunc(quotedate,'MI') +
> > trunc(to_number(to_char(quotedate,'SS'))/15)*15/60/60/24
> > from quote where quotedate > '2003/09/22';
> >
> >This one rounds *down*. If you want to round *up*, it's ;
> >
> >select trunc(quotedate,'MI') +
> >((trunc(to_number(to_char(sysdate,'SS'))/15)*15)+15)/60/60/24 from
> >quote where quotedate > '2003/09/22';
> >
> >
> >- Kenneth Koenraadt
> >
> >
> >On Mon, 22 Sep 2003 10:12:32 -0500, "Jake" <me_at_heyjay.com> wrote:
> >
> >>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 - 21:53:08 CDT
