Two database fields for date and time - should they be merged?

Question

In the following question, field and table names have been changed to protect their identities.

If I have two database columns:

MONKEY_DATE DATETIME NULL (with data e.g. 2012-05-14 00:00:00.000)
MONKEY_TIME DATETIME NULL (with data e.g. 1753-01-01 16:30:53.025)

The date component of the time field is mostly set to 1st Jan 1753... but some data has 1st Jan 1899 and some has 1st Jan 1900.

I find that maintaining the code to query and report on these columns causes me (and our team) a headache that could easily be solved by merging the two columns. However, experience (and Terry Goodkind) has taught me that nothing is ever easy. See below some examples of why this is a headache.

My Approach

I'm thinking the following approach will have the desired effect of merging the two columns:

1. Use SQL to update the data, setting the value for the date field and the value for the time field both to the same value, which is a mix of the date component from the date field and the time component from the time field
2. Write any new code only using the MONKEY_DATE field
3. Eventually phase out the MONKEY_TIME field and any of the date/time component SQL (see examples)
4. Drop MONKEY_TIME

This will mean we don't immediately have to go and make retrospective changes to the whole system... all existing code will continue to work... and we can begin to do things The Right Way.

SQL for #1 might be (Oracle):

UPDATE MONKEY SET 
    MONKEY_DATE = TO_DATE(TO_CHAR(MONKEY_DATE, 'MM/DD/YYYY ') || 
                      TO_CHAR(MONKEY_TIME, 'HH24:MI:SS'), 
                      'MM/DD/YYYY HH24:MI:SS')
    MONKEY_TIME = TO_DATE(TO_CHAR(MONKEY_DATE, 'MM/DD/YYYY ') || 
                      TO_CHAR(MONKEY_TIME, 'HH24:MI:SS'), 
                      'MM/DD/YYYY HH24:MI:SS')

The Question

My questions to you are:

• Should these fields be merged?
• Is my approach reasonable to merge these two columns?
• Do you think it would be better to skip steps two and three?
• Do you have any other (constructive) comments or suggestions?

Examples

For example, to select all my monkey dates and times and order them by date and time, I need to do something like this (SQL Server):

SELECT 
      CONVERT(DATETIME, CONVERT(VARCHAR, MONKEY_DATE, 101), 101) AS MONKEY_DATE
    , CONVERT(DATETIME, CONVERT(VARCHAR, MONKEY_TIME, 108), 108) AS MONKEY_TIME 
FROM MONKEY 
ORDER BY
      CONVERT(DATETIME, CONVERT(VARCHAR, MONKEY_DATE, 101), 101) DESC
    , CONVERT(DATETIME, CONVERT(VARCHAR, MONKEY_TIME, 108), 108) DESC

or this (Oracle - slightly more explicit):

SELECT
      TO_DATE(TO_CHAR(MONKEY_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') AS MONKEY_DATE
    , TO_DATE(TO_CHAR(MONKEY_TIME, 'HH24:MI:SS'), 'HH24:MI:SS') AS MONKEY_TIME
FROM MONKEY
ORDER BY
      TO_DATE(TO_CHAR(MONKEY_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') DESC
    , TO_DATE(TO_CHAR(MONKEY_TIME, 'HH24:MI:SS'), 'HH24:MI:SS') DESC

I also often find myself selecting a merged date/time column (Oracle):

SELECT 
    TO_DATE(TO_CHAR(MONKEY_DATE, 'MM/DD/YYYY ') || 
            TO_CHAR(MONKEY_TIME, 'HH24:MI:SS'), 
        'MM/DD/YYYY HH24:MI:SS') AS MONKEY_DATE_TIME 
FROM MONKEY

Because, almost all the time, we want to know the date and time of the monkey.

The above SQL could be easily altered to:

SELECT MONKEY_DATE_TIME FROM MONKEY ORDER BY MONKEY_DATE_TIME

... If only we had merged columns.

Background

I've inherited an old ASP system that stores dates and times in separate columns in the database. I've been told this is probably because the application started out in an early version of Access, where it was not possible to store both date and time in the same column. The whys and hows aren't really part of this question, but some people like to know.

P.S.

I really nearly posted this in SO.SE, so my apologies if I got the wrong site.

Answer 1

One minor point: WHEN you merge the two columns, you might want to do the merge into a new "MONKEY_DATE_2" column instead of overwriting the existing one. That leaves your current columns unchanged, and you can find all the code that hasn't been updated to work with the new structure with grep.

Answer 2

Yes, I think they should be merged. I normally wouldn't bother with separating date and time fields unless there is good reason to do it. Legacy systems might have been a good reason but if the data had migrated to a system that can handle dates and times combined, then merging is a good idea.

As for your approach, it sounds reasonable. You might want to even run a small refactoring project to fix all the code at the same time to make sure that all of your queries are corrected together to get rid of the "Eventually phase out the MONKEY_TIME field", though it could take some time and it will probably require significant regression testing. Which shouldn't be a problem if you plan ahead for it.

Also investigate if there's any downstream systems (such as web services or external reporting systems) that are built from different code bases but still depend on separate date and time values. If such systems exist, they will also have to be a part of this plan.

Answer 3

If the date and time are always used together, then by all means, merge the columns and reap the benefits of fewer headaches.

Things to watch out for:

• Use of the time column for calculating relative time across days (e.g., "select of the monkeys that went bananas on any day at a time within one hour of when this monkey went bananas").
• Arithmetic on the date column that doesn't deal with fractional days in a sane way.
• Use of the date column as a grouping mechanism.

If you have existing queries that are particularly sticky, create an updatable view that emulates the old behavior until you can get them straightened out.

Answer 4

I had a similar problem at a previous work term. We split date and time into two DB columns. This caused us plenty of headaches. >_< With that said, I would highly recommend that you switch to a single datetime column in your DB. This will keep a lot of bugs from creeping up.

As for your strategy, it sounds reasonable, but make sure you get the whole team involved in this decision and refactoring. You need actively to discourage anyone from using the old data schema.

If there's not a lot of code changes required(and you have some extra time!), you can consider make the change all at once and not having an "intermediate" step where you support both data schemas. However, this is usually unlikely so you will probably need to have some sort of migration plan like the one you mentioned in step 2/3

Answer 5

If you are phasing in this change over time (as opposed to preparing all the changes and then installing everything at once), you need to be careful that you don't read values the new way when it was written in the old way. So the transition would need to go:

1. All new writes both the new way and the old way (using a new column for the new way would help), and reads the old way. Existing code is modified to write both tne new way and the old way.

2. Once all code is writing both ways, convert existing data so it is available both ways.

3. All new code reads the new way (and still writes both ways). Existing code is modified to read the new way.

4. Once all code is reading the new way, new code can write only the new way, and existing code can be modified to only write the new way.

5. Once all code is reading and writing the new way, and no code references the old columns, they can be removed.

The new way (one column with both date and time) seems obviously better to me, you have to decide if it is enough of an improvement to go through the conversion process.