Re: rounding to 15 seconds

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 - 14:25:48 CDT