Re: Date and DateTime columns

markg_at_mymail.tm (MarkyG) wrote in message news:<ab87195e.0201090057.487f22fd_at_posting.google.com>...
> Personally i would stick with the first method, its the more standard
> and the way everyone knows about.
>
> Or you can have your date field and two extra fields with your xxx_dt
> and xxx_ts and populate them with a database trigger. Then your users
> can have a choice in what to use.
>
> M
>
> Jim Poe <jpoe_at_fulcrumit.com> wrote in message news:<MPG.16a5488daf42a8ff989680_at_news.fulcrumit.com>...
> > Hi,
> >
> > Using Oracle 8, Delphi 5, Midas
> >
> > This is a design question. We have approximately 65 date fields. A
> > little less than half (30) of them are required to be timestamps. The
> > rest only need to be concerned with the date portion.
> >
> > We are having an internal debate on how to treat these fields.
> >
> > One faction wants to treat all the date columns the same and populate
> > them with the date and current time, a timestamp. This would mean that
> > all date columns would be handled consistently. It would require that
> > any date only comparisons and selects would need to truncate the time.
> > This would need to be done in our sql code, our Delphi application code,
> > and any tools that the end user would use.
> >
> > The other faction wants to specifically class the two date types. All
> > date only fields would have a naming convention of XXX_DT. They would
> > be populated with the date and 00:00:00 time. In code we would always
> > use
> > MyDate := Date;
> > The timestamp fields would have a naming convention of XXX_TS. They
> > would be populated with a full date/time timestamp.
> > MyDate := Now;
> > When manipulating the dates, the developer and end user would know when
> > they have to truncate time by the column name being in the format
> > XXX_TS. Any XXX_DT columns could use the raw value for comparisons and
> > selects.
> >
> > Are there any other issues we are not aware of? How do others handle
> > this situation?
> >
> > Thanks

I believe that some thought should be given to the use of views for data selection as the views can contain the code/functions necessary to format the date and time data as desired for output. Multiple date and time version columns could be provided for use based on one real column that stores the data. This can provide a very convienent means of providing the most desired formats: Mon dd YYYY, number of seconds since Jan 1 1970, etc ....

The use of triggers to normalize the data on input and update has already been mentioned.

• Mark D Powell --