Store Date-time Data As Numbers

The current project I’m working on uses an Oracle DBMS to store data. During development I found that Date information is not stored in a Date field, but in a VARCHAR2 column wi

Solution 1:

It would be much better if the dates were stored as dates. Storing them as numbers rather than strings introduces a different set of problems.

If you are absolutely stuck with dates stored as strings, in order to allow indexes on the columns to be used, you'd need to convert the dates you're using as parameters as strings in the appropriate format and then rely on the fact that sorting of strings in that particular format matches the expected sort order of actual dates. If you ever compare the string to date or to a number, you're going to get implicit data type conversion which, at best, will lead to performance problems because indexes cannot be used and at worst will generate incorrect results or errors.

Assuming you avoid data type conversion, the performance issues are likely to arise from the fact that the optimizer has a great deal of difficulty estimating cardinality when you use the wrong data type. Oracle knows, for example, that there are 365 days (or 8760 hours or 525600 minutes) between 1/1/2012 and 1/1/2013. On the other hand, there are billions of possible strings between '20120101000000' and '20130101000000'. That can cause the optimizer not to use an index when you would like it to (or vice versa), to use the wrong sort of join, etc.

Solution 2:

In general, it is better if they are stored as dates. You can convert them using:

to_char(<field>, <formatstring>)

And I think the format string 'YYYYMMDDHHMISS' works, but I'm not positive.

However, there might be a reason why they chose this format. Oracle stores date/times as a numbers. Extracting year, month, day, hour minutes, and seconds requires a bit of mathematical manipulation. Depending on the processing environment, it might be much easier to use substring operations to extract date components.

My guess is that if the code is using these fields, then there are multiple examples where string operations are being used. This seems like an intentional design decision, so check things out carefully before changing it (to what is a better solution).