How would I store a date that can be partial (i.e. just the year, maybe the month too) and output it later with the same specifity?

Question

I want to let users specify a date that may or may not include a day and month (but will have at least the year.) The problem is when it is stored as a datetime in the DB; the missing day/month will be saved as default values and I'll lose the original format and meaning of the date.

My idea was to store the real format in a column as a string in addition to the datetime column. Then I could use the string column whenever I have to display the date and the datetime for everything else. The downside is an extra column for every date column in the table I want to display, and printing localized dates won't be as easy since I can't rely on the datetime value... I'll probably have to parse the string.

I'm hoping I've overlooked something and there might be an easier way.

(Note I'm using Rails if it matters for a solution.)

Answer 1

in your model only pay attention to the parts you care about. So you can store the entire date in your db, but you coalesce it before displaying it to the user.

Answer 2

Is it really necessary to store it as a datetime at all ? If not stored it as a string 2008 or 2008-8 or 2008-8-1 - split the string on hyphens when you pull it out and you're able to establish how specific the original input was

Answer 3

I'd probably store the datetime and an additional "precision" column to determine how to output it. For output, the precision column can map to a column that contains the corresponding formatting string ("YYYY-mm", etc) or it can contain the formatting string itself.

Answer 4

The additional column could simple be used for specifying what part of the date time has been specified

1 = day 2 = month 4 = year

so 3 is day and month, 6 is month and year, 7 is all three. its a simple int at that point

Answer 5

I don't know a lot about DB design, but I think a clean way to do it would be with boolean columns indicating if the user has input month and day (one column for each). Then, to save the given date, you would:

1. Store the date that the user input in a datetime column;
2. Set the boolean month column if the user has picked a month;
3. Set the boolean day column if the user has picked a day.

This way you know which parts of the datetime you can trust (i.e. what was input by the user).

Edit: it also would be much easier to understand than having an int field with cryptic values!

Answer 6

As proposed by Jhenzie, create a bitmask to show which parts of the date have been specified. 1 = Year, 2 = Month, 4 = Day, 8 = Hour (if you decide to get more specific) and then store that into another field.

The only way that I could think of doing it without requiring extra columns in your table would be to use jhenzie's method of using a bitmask, and then store that bitmask into the seconds part of your datetime column.

Answer 7

The informix database has this facility. When you define a date field you also specify a mask of the desired time & date attributes. Only these fields count when doing comparisons.

Answer 8

With varying levels of specificity, your best bet is to store them as simple nullable ints. Year, Month, Day. You can encapsulate the display logic in your presentation model or a Value Object in your domain.

Answer 9

Built-in time types represent an instant in time. You can use the built in types and create a column for precision (Year, Month, Day, Hour, Etc.) or you can create your own date structure and use nulls (or another invalid value) for empty portions.

Answer 10

If you store a string, don't partially reinvent ISO 8601 standard which covers the case you describe and more:

http://en.wikipedia.org/wiki/ISO_8601