Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: rounding to 15 seconds

Re: rounding to 15 seconds

From: Jake <me_at_heyjay.com>
Date: Mon, 22 Sep 2003 21:52:03 -0500
Message-ID: <bkockf$ed7$1@bob.news.rcn.net>


Thanks Mihai

Jake

"Mihai Pora" <mihai.pora_at_hp.com> wrote in message news:TiFbb.5344$IR6.2871_at_news.cpqcorp.net...
> 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 - 21:52:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US