Storing partial dates in a database

Question

I want to store partial dates in a relational database (MySQL, PostgreSQL, etc.). For example, input can only be a year (2013); year and month (2013-08); or year, month, and day (2013-08-29). I can’t just use the regular DATE type, as the year will be expanded to 2013-01-01, and it cannot be distinguished from year, month and day.

I thought about separating the date to three separate fields (year, month, and day as integers), but I lost all the dates in DBS and have to manage a lot of pointers.

My other thought is to save it as a DATE and have another column telling how accurate the date is. For example, “2013-08-01” and “month” mean that the date will only be accurate to the month (2013-08). "2013-08-01" and "day" mean that the date will be fully 2013-08-01.

What is the best way to do this?

Answer 1

I think there are two possible ways:

(1) Save date substrings , for example:

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

(2) Save 3 different columns , Year, Month, Day (and you can create an index of Year + Month + Date without any problems)

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

Which one is best depends on how you want to request data. Suppose you have a stored procedure, and you want to pass a parameter so that all rows fall into state.

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

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

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

 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) 

This can be more complicated depending on how you want to process the strings. For example, if you specify a parameter of type 2013-01 , do you want to get strings where month = null or not?

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

Answer 2

Perhaps the best way is to treat them as time intervals and have effdate and enddate . You can represent any period of time that you want. The year will be similar to "2012-01-01" and "2012-12-31". A single date will look like "2013-08-28" and "2013-08-28".

It will also give you the flexibility to expand your view to handle quarters or other time groups.

Answer 3

From what you have indicated, you do not want to use date_trunc , because you want 2013-08 mean “the month of August 2013”, and not the actual date! That way, you seem to care less about moments and periods.

I think you just need to store the strings. If you store:

 2013 2013-08 2013-09-25 

like strings, you should be fine. They look just fine. In addition, you only need one column. If you use two columns, your data may look funny. For example, if you store

 2013-09-04 | MONTH 

one may wonder why there is 4 .

Then you can get the wrong line with my suggestion.

Another idea is to make your lines ISO 8601 time intervals . Your database system may even have a time slot type.