Data Type "DATE" vs "DATETIME YEAR TO DAY"
Posted in 1995
I have a piece of attribute called "destruction date" and it's computed from two smallint attributes (julian date and retention period). The application (ESQL/C) needs to find all the records by a given year or between dates, for example between 01/01/1994 and 12/31/1994, after the system runs for a period of time. First I chose "DATE" for "destruction date" since type DATE, by Informix, is a 4-bytes integer. Since the table contains "destruction date" is going to be very large and the DBA will have an index on "destruction date". The DBA thinks the data type should be "DATETIME YEAR TO DAY" since "destruction date" will be stored in a string as "YYYY/MM/DD" and SELECT queries on a given year or between two dates will perform better via the index (although the application has to do conversion in order to store an integer date into a "DATETIME YEAR TO DAY" column). I would like to know which data type (DATE or DATETIME YEAR TO DAY) makes better performance for our circumtance. Thanks in advance for any input ! Diana Li