OHDSI Home | Forums | Wiki | Github

CONDITION_END_ DATE as '2999-12-31' instead of NULL?

Being a programmer first, I have a deep hatred of NULL’s, as they commonly break logic and/or make the query analyzer misbehave. Is there any reason that I cannot use ‘2999-12-31’ instead of NULLS?

Yes, because you will be the only one that knows ‘2999-12-31’ means NULL. Besides, you are going to find that to be inclusive, the CDM has many columns that you will not have the source data to fill and will have to set these columns to NULL. Get used to it now. :slightly_smiling_face:

‘2999-12-31’ would not mean NULL, it would mean that the condition is still valid. One cannot do either a comparison, nor a sort, on NULLs.

I will insert NULLs but I will never get used to it; I have watched too much software blowup due to them. :slight_smile:

Thank you

This (using null end dates) has come up in the past, and the problem is that saying ‘ongoing conditions’ or ‘ongoing visits’ isn’t something you represent in the CDM. Everyone has an observation_period, and things shouldn’t extend past their observation period.

Taking your example with the end date of 2999-12-31, what if you ask your data ‘What’s the average duration of condition X’ and some of the people have 1700 years of duration…isn’t that a problem?

And the statement of ‘ongoing’ is also probelmatic…if you use getdate() as a fill-in for null end dates for purposes of establishing end dates, then as time goes on, the duration of the condition gets longer and longer beacuse as you move forward in ‘reality time’, the getdate() becomes later, and you get different values of duration (start date → getdate()).

@Mark:

You are totally right. We have one convention in the vocabulary tables, and another one in the data tables. Could have been done better. It’s hard to change now, there are too many tools and scripts depending on it. You’d need a time machine and travel back like 12 years.

However, there are a bunch of caveats:

  1. In the vocabulary tables 2099-12-31 (not 2999 btw) means “still ongoing”. In the data tables we actually don’t know whether it is still ongoing. It might not, we just don’t know. If patients have a disease we know when that started (they show up at the doctor’s office and get recorded). We don’t know when it healed. Because patients just stop showing up. I am sure you yourself don’t get a doctor’s appointment to declare “I am fine now”. :slight_smile: But sometimes we do do. Let’s say the patient is choking and somebody applies the Heimlich maneuver. Or the appendicitis is over after the appendectomy. So, NULL means “ongoing” or “we don’t know”.
  2. NULL is ugly, because you need to process it using different expressions, but so is 2099-31-12. Because you need to catch that each time. For example, you can’t just use end_date-start_date to calculate, say, an average or median duration of something. You’d have to put a case statement in removing the end-of-century end dates. But avg(end_date-start_date) works perfectly fine with NULLs in it because those are just dismissed, which is what you want.

Bottom line: It is what it is.

@Chris_Knoll and I overlapped saying the same thing. Not the first time.

OK, that makes sense. We are outpatient only, so this type of data is very sparse.

Internally, I use ‘2999-12-31’, only because I expect some patients to still be in practice at the turn of the next century, barring another Carrington event.

I think this comes down to my experiences in other fields outside of medicine; that and the different ways different DB/languages handle NULLs.

Thank each of you for your feedback in helping me understand how this is being used behind the covers; the reasons makes much more sense now.

Oddly, as a database developer, I have a near-religious reverence for them. :slightly_smiling_face:

Oh yes. On January 1st, 2100 the OMOP CDM as we know it will break down. Good news: Neither you, nor me or anybody else in the OHDSI community will experience that. Why would we not leave some problem for our children and grandchildren to solve? :slight_smile:

1 Like
t