Storing partial dates in a database

Question

I want to store partial dates in a relational database (MySQL, PostgreSQL, etc.). For example, input may just be the year (2013); the year and month (2013-08); or the year, month, and day (2013-08-29). I cannot just use a normal DATE type, as the year will be expanded to 2013-01-01, and this indistinguishable from having the year, month, and day.

I have thought of either separating the date into three separate fields (year, month, and day as integers), but I lose all date niceties in the DBS and have to manage more indicies.

My other thought is to store it as a DATE and have another column saying how precise the date is. For example, '2013-08-01' and 'month' would mean the date is only precise up to the month (2013-08). '2013-08-01' and 'day' would mean the date is fully 2013-08-01.

What's the best way to do this?

Answer 1

I think there're two possible ways to go:

(1) Store substrings of date, like:

'2013'               -- year 2013 
'2013-01'            -- year 2013, January
'2013-01-01'         -- year 2013, January 1

(2) Store 3 different columns, Year, Month, Day (and you can build index Year + Month + Date with no proble)

2013  null  null     -- year 2013 
2013     1  null     -- year 2013, January
2013     1     1     -- year 2013, January 1st

Which one is the best depends on how do you want to query data. Suppose you have stored procedure and you want to pass a parameter to get all rows falling into condition.

In case (1), you pass string @Date = '2013-01' as a parameter and you want to get all rows where year = 2013 and month = 01. So the where clause would be like:

where left(Date, len(@Date)) = @Date

In case (2), you pass three parameters - @Year = 2013, @Month = 1, @Day = null and the where clause would be something like:

where
    Year = @Year and -- Supposing @Year is always not null
    (@Month is null or @Month is not null and Month = @Month) and
    (@Day is null or @Day is not null and Day = @Day)

It could be more complex depending on how do you want to process rows. For example, if you give parameter like 2013-01, do you want to get rows where month = null or not?

On the other hand, if you want to pass date and check if it falls into date range, then Gordon Linoff suggestion is a good one to use.

Answer 2

Perhaps the best way is to treat these as spans of time and to have an effdate and enddate. The you can represent any time span you want. A year would be like '2012-01-01' and '2012-12-31'. A single date would be like '2013-08-28' and '2013-08-28'.

This would also give you the flexibility to expand the representation to handle quarters or other groups of time.