Re: Date and DateTime columns

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 18 Jan 2002 22:57:07 -0600
Message-ID: <u8zauvkju.fsf@rcn.com>

On 09 Jan 2002, jpoe_at_fulcrumit.com wrote:
> 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.

Maybe you can have a date table and your time can just be an integer with the number of seconds from Midnight?

Then, you can have two fields if you need date and time, and you have one field when you need just date. The date table then can then sometimes also add easier date logic comparison. You may find it pleasing that you don't use date functions for _any_ date calculations. You join to the date table and use straight SQL. Down the road, you can also add more fields in your pre-built date table that help with new queries.

create table date_tbl(

DATE_ID                 integer
Day                     integer
month                   integer
year                    integer
month_nbr               integer
quarter_nbr             integer
julian_month            integer
week_number             integer
vacation_ind            yes/no
financial_quarter_nbr   integer
need_a_sick_day_ind     yes/no  :-)

...
)

You store the DATE_ID in your date fields instead of a real date and then you can have your app handle date logic by joining your tables against the date table.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Fri Jan 18 2002 - 22:57:07 CST