Re: Store TIME in the Oracle
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: "Dunbar, Norman (Capgemini)" <norman.dunbar.capgemini@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 15 Aug 2011 08:44:42 -0300
Morning Norman,
The datatype will be DATE., as you said.
I have interest only on TIME and not in DATE.
I will store it complete as DATE + TIME and retrieve only TIME.
You are right, and I think I am doing as you said, right ?
Regards
Eriovaldo
On Mon, Aug 15, 2011 at 4:16 AM, Dunbar, Norman (Capgemini) <
norman.dunbar.capgemini@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> Morning,
>
> >> I will follow this way:
> >>
> >> to_date('01-JAN-2000 '||
> >> to_char(sysdate,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
> >>
> >>
> >> I will put the date hard code for all lines inserted and the
> >> time that must be saved.
>
> I'm not convinced that that is a good idea. I have a nagging feeling in my
> head that this will confuse the optimiser if it ever needs to take into
> account this column in working out a decent query plan.
>
> Equally, you'll probably never get the benefit of an index on that column I
> should imagine - but I suspect you won't be using one anyway.
>
> Why not just set your column to sysdate?
>
> You will save on one concatenation, on to_char() and one to_date() function
> call for every insert. As a quick and highly unscientific test, it takes
> around 23.8% longer to do it your way as compared to sysdate only. Over
> three runs of 1,000,000 rows inserted. (Very slow PC running Oracle and the
> database on one single disc.)
>
> The date part is irrelevant as you will be selecting only the time part, so
> why not save some CPU etc?
>
> Just my £0.02.
>
>
> Cheers,
> Norm.
>
> Norman Dunbar
> Contract Senior Oracle DBA
> Capgemini Database Team (EA)
> Internal : 7 28 2051
> External : 0113 231 2051
>
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify the
> sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you should
> still check any attachment before opening it.
> We may have to make this message and any reply to it public if asked to
> under the Freedom of Information Act, Data Protection Act or for litigation.
> Email messages and attachments sent to or from any Environment Agency
> address may also be accessed by someone other than the sender or recipient,
> for business purposes.
>
> If we have sent you information and you wish to use it please read our
> terms and conditions which you can get by calling us on 08708 506 506. Find
> out more about the Environment Agency at www.environment-agency.gov.uk
>
Other related posts:
