Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
Commit to merge. Once commited - cut! –  Oded May 14 '12 at 15:54
add comment

5 Answers

up vote 13 down vote accepted

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.

share|improve this answer
5  
+1. Thats exactly what I was going to suggest (with the exception that I would call the new column MONKEY_DATETIME). –  Doc Brown May 14 '12 at 16:26
5  
And don't forget to add triggers that update the old columns when the new one changes and vice versa. –  Blrfl May 14 '12 at 18:32
    
mmm, yes. Triggers. At least until the axe falls... :) –  mjfgates May 15 '12 at 12:34
add comment

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.

share|improve this answer
1  
+1 for downstream systems. We have a few APIs that would need to be checked... Hmm, and it may affect third-party systems that use the APIs. I'll have to think about that, thanks. –  LordScree May 14 '12 at 16:00
1  
Haha. Both of answers have "As for your *, it sounds reasonable". Great minds think alike? :P –  Oleksi May 14 '12 at 16:00
    
If the legacy system is essentially still in place, be sure to check the code that references those dates is appropriately wrapped to reference only the date or only the time when appropriate. –  mikebabcock Oct 23 '12 at 21:15
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.