Re: Daylight Saving Code

As I said you can make it a function or you don't have to. See 2nd sentence. In my example I am updating multiple rows (assuming there are multiple rows in the table). OF course, I've actually tried this and actually found that as written it is very slow and I had to go to packages and use constants initialized in the package body. (using constants in a function did not work very efficiently - again the multiple evaluation problem.

Alex,
1. You should be more careful reading what people wrote. 2. Benchmark it instead of immediately saying BS.

Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Alex Filonov" <afilonov_at_yahoo.com> wrote in message
news:336da121.0306130626.2e2c25d0_at_posting.google.com...


> "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message

news:<LcaGa.171841$M01.80694_at_sccrnsc02>...


> > nope.  tried that first.  Why is his way slower?

> >

> > let's say he makes it a function so one can do:(I'm making it a function

so


> > I don't type the whole thing, but you don't have to make it a function,

you


> > can type the darn thing)

> > update mytable set mydate=convert_to_daylight(mydate) where ...

> >

> > For every row it is going to have to evaluate to_date something like 15

or


> > 16 times.  In my case once he calls the package to_date is evaluated (to

set


> > up the constants) 15 or 16 times total.

> >

>

> BS. There is no need to create a function. Just write subquery :

>

> update mytable set mydate = (select ...)

>

> Oracle, at least since v8.0, calculates expressions with literals only

> once per SQL statement. So, in your case, it does 16 calculations, exactly

> like with you package. Of course, if separate update statement is run for

> every row updated, it's going to take much more time. But who said you

> need to do that? Just run update for all rows you need to update.

>

> > So 15 times * # of rows is going to be slower than 15 times per session

that


> > calls the package.  I took an update from 45 minutes to 2 doing this

very


> > technique. (thanks to the asktom site. Thanks Tom Kyte)

> >

> > Jim

> >

> >

> >

> > -- 

> > Replace part of the email address: kennedy-down_with_spammers_at_attbi.com

> > with family.  Remove the negative part, keep the minus sign.  You can

figure


> > it out.

> > "Alex Filonov" <afilonov_at_yahoo.com> wrote in message

> > news:336da121.0306120643.7cbcd533_at_posting.google.com...

> > > "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message

> >  news:<fgUFa.1206020$F1.142726_at_sccrnsc04>...

> > > > I've done this before and you are better off using a package. (I am

not


> >  sure

> > > > your code works, there were some dates during the late 70's where

the


> > > > daylight savings was something like January and Feb.)  Also in a

package


> >  you

> > > > can declare some constants and thus if you are using it in an update

> > > > statement it will be much more efficient.

> > > > (the constants are private to the package body and thus initialized

once


> >  per

> > > > session when the package is first used - otherwise you are calling

> >  to_date

> > > > to_char a zillion times per row in an update statement. (very

> >  inefficient)

> > > > Jim

> > > >

> > >

> > > Nah. Oracle has operations with dual hardcoded in the server, so they

> > > are incredibly fast. Package would be slower and use more resources

> > > (those global variables are allocated somehow).

> > >

> > > > -- 

> > > > Replace part of the email address:

kennedy-down_with_spammers_at_attbi.com


> > > > with family.  Remove the negative part, keep the minus sign.  You

can


> >  figure

> > > > it out.

> > > > "Aruneesh Salhotra" <asalhotra_at_customink.com> wrote in message

> > > > news:a29eb1e7.0306110843.5c979d6e_at_posting.google.com...

> > > > > I created a oracle query which detects the start and end date for

> > > > > dayight saving. I eventually used this, and passed the current

year so


> > > > > to detect start/end dates for daylight saving for each year.

> > > > >

> > > > > I wanted to share the same with everyone.

> > > > >

> > > > > select SYSDATE x, 0 r1 from dual where SYSDATE between (select

> > > > > decode(to_char(to_date('04/01/2003','MM/DD/YYYY'),'D'),1,

> > > > > to_date('04/01/2003','MM/DD/YYYY')+7, 2,

> > > > > to_date('04/01/2003','MM/DD/YYYY')+6, 3,

> > > > > to_date('04/01/2003','MM/DD/YYYY')+5, 4,

> > > > > to_date('04/01/2003','MM/DD/YYYY')+4, 5,

> > > > > to_date('04/01/2003','MM/DD/YYYY')+3, 6,

> > > > > to_date('04/01/2003','MM/DD/YYYY')+2,

> > > > > to_date('04/01/2003','MM/DD/YYYY'))  sday1 from dual) and (select

> > > > > decode(to_char(to_date('09/01/2003','MM/DD/YYYY'),'D'),1,

> > > > > to_date('09/01/2003','MM/DD/YYYY')+7, 2,

> > > > > to_date('09/01/2003','MM/DD/YYYY')+6, 3,

> > > > > to_date('09/01/2003','MM/DD/YYYY')+5, 4,

> > > > > to_date('09/01/2003','MM/DD/YYYY')+4, 5,

> > > > > to_date('09/01/2003','MM/DD/YYYY')+3, 6,

> > > > > to_date('09/01/2003','MM/DD/YYYY')+2,

> > > > > to_date('09/01/2003','MM/DD/YYYY'))  sday2 from dual);