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.

This is a problem I've run into a few times. Imagine you have a record that you want to store into a database table. This table has a DateTime column called "date_created". This one particular record was created a long time ago, and you're not really sure about the exact date, but you know the year and month. Other records you know just the year. Other records you know the day, month and year.

You can't use a DateTime field, because "May 1978" isn't a valid date. If you split it up into multiple columns, you lose the ability to query. Has anyone else run into this, if so how did you handle it?

Edit: To clarify the system I'm building, it is a system that tracks archives. Some content was produced a long time ago, and all that we know is "May 1978". I could store it as May 1 1978, but only with some way to denote that this date is only accurate to the month. That way some years later when I'm retrieving that archive, I'm not confused when the dates don't match up.

For my purposes, it is important to differentiate "unknown day in May 1978" with "May 1st, 1978". Also, I would not want to store the unknowns as 0, like "May 0, 1978" because most database systems will reject that as an invalid date value.

share|improve this question
14  
Is it important to differentiate "unknown day in May 1978" with "May 1st, 1978"? –  MichaelT Apr 8 '13 at 15:18
4  
@MichaelT: yes, it is important to differentiate. –  nbv4 Apr 8 '13 at 15:19
6  
martinfowler.com/eaaDev/TimePoint.html –  psr Apr 8 '13 at 18:13
6  
@aslum: Most database systems will reject that as an invalid date value –  nbv4 Apr 8 '13 at 19:12
9  
@JimmyHoffa - you've never run into a fuzzy dates scenario, or one where you needed to compare dates? In either case, a common one is a medical history: you remember that the appendectomy was last year on April 1, but the tonsilectomy was sometime in 1975, and something else happened during May and June of some year. What if you want to know if some medical event was before or after some other medical breakthrough? Did this happen before or after they were checking the blood supplies for HIV? –  thursdaysgeek Apr 8 '13 at 21:31

16 Answers 16

Store all dates in normal DATE field in the database and have additional accuracy field how accurate DATE field actually is.

date_created DATE,
date_created_accuracy INTEGER, 

date_created_accuracy: 1 = exact date, 2 = month, 3 = year.

If your date is fuzzy (e.g May 1980) store it at start of period (e.g. May 1st. 1980). Or if your date is accurate to year (e.g. 1980) store it as January 1st. 1980 with corresponding accuracy value.

This way can easily query in a somewhat natural way and still have notion how accurate dates are. For example this allows you to query dates between Jan 1st 1980 and Feb 28th 1981, and get fuzzy dates 1980 and May 1980.

share|improve this answer
1  
You still have to calculate date-end here from what I can see, so I think that in between query is pretty ugly as you've got a calculated field you are selecting upon at best. –  Wyatt Barnett Apr 8 '13 at 19:56
8  
Nice answer, really smart. select * from mytable where date_created between "1980/1/1" and "1981/2/28" and date_created_accuracy <= 2;. Genius. –  Naftuli Tzvi Kay Apr 8 '13 at 21:21
54  
I would encourage you to consider the date accuracy to be simply "days". Where an exact day is 0. This way one can use more flexible dates "Sometime in the summer" having a date accuracy of 90 days based off of June 1st rather than hard encoded specific date ranges. It could also handle multi-year accuracy. –  MichaelT Apr 8 '13 at 21:45
1  
You should maybe submit that as an answer, MichaelT –  Supr Apr 9 '13 at 9:45

If you don't need to use this kind of data as regular date-time information, any simple string format would do.

But if you need to keep all the functionality, there are two workarounds I can think of, both requiring additional information stored in the database:

  1. Create min date and max date fields, which have different values for "incomplete" data, but will coincide for accurate dates.
  2. Create types for each kind of inaccurate date (none _ 0, date_missing _ 1, month_missing _ 2, year_missing_4, etc _ so you can combine them). Add a type field to the records and keep which information is missing.
share|improve this answer
1  
Long startup ago, we had to solve the exact same issue. Users could tell stories about events that happened any time in the past, so we had to support fuzzy dates. After much back-and-forth, the solution we arrived at is most similar to superM's suggestion here, where dates are stored as the min & max possible instants that would contain the story's date. When reporting the date, the accuracy (i.e. "this record is accurate to the month/year/day") can be extracted from the delta between the min & max dates. No need to store a 3rd field for accuracy. –  meetamit Apr 8 '13 at 16:47
2  
+1 for min date and max date fields. I think that is the most flexible, yet precise and easy to use solution. –  Supr Apr 9 '13 at 11:13
1  
I was antagonistic to this idea at first. But realizing it is the most flexible approach, I vote for this. –  Anurag Kalia Apr 9 '13 at 11:47

This is really more of a requirements definition than a technical problem -- what you need to focus on is "how can we define the dates in the past" and the technical solution will flow.

The times I've had to approach something like this we've typically:

  • Define how to map things -- like MichaelT suggests, decide that anything that is defined as Month/Day becomes defined as midnight on the 1st of said month. This is typically good enough for most purposes -- if the exact date was that important you probably would have a record of it 35 years later, right?
  • Figure out if you need to track this -- IE, do records with slightly made up create dates need a flag saying so? Or is that just a user training issue so folks know and can act accordingly.

Sometimes one needs to do something like make the dates fuzzy -- for example, that may one date might need to respond to a query for anything in May 1978. This is doable -- just make your create_date 2 fields, old records get a 30 days spread as appropriate, new ones get 2 identical values.

share|improve this answer
1  
+1 - I was working on formulating an answer with the double date approach. Your answer got here first. –  MichaelT Apr 8 '13 at 15:29
2  
+1, It's ugly and creates a lot of useless extra information for the new entries that don't require it, but on the other hand it does keep queries much simpler than they otherwise would be. We've been using a similar solution for a related issue for a while now. –  Izkata Apr 8 '13 at 17:05
3  
@Izkata -- Fair point, but how elegant can you get when you need to make something that should be a single point in time span a month. Certainly prettier than having to calculate start and end for queries on the fly somewhere. –  Wyatt Barnett Apr 8 '13 at 19:58
1  
+1 for being able to denote arbitrary granularity without an explosion of enum values. –  Dan Neely Apr 8 '13 at 20:56

The simplest way to denote if the date is accurate is to create an accuracy field INT(1) with default NULL

If the date is accurate store date-time in "date_created" & leave accuracy NULL

If date is only accurate to month store date-time as 1st of Month with accuracy value 1

If date is only accurate for year store date-time 1st of January with accuracy value 2

You can use different numbers to hold different values such as first quarter etc

share|improve this answer
3  
This has difficulty with data that isn't on a clean month boundary such as "Q2 1991" and "Winter 1978-1979". –  MichaelT Apr 8 '13 at 17:37
1  
OP wants some way to denote that this date is only accurate to the month. –  david strachan Apr 8 '13 at 18:32
5  
You are abusing the meaning of NULL here. NULL means "unknown", so if the date is accurate, accuracy can not be NULL. It can be '1'. –  Konerak Apr 9 '13 at 10:57

In the past I've stored dates-with-accuracy as a start date and an end date. The day may21,2012 would be represented as start=12am,may21,2012 and end=12am,may22,2012. The year 2012 would be represented as start=12am,Jan1,2012 end=12am,Jan1,2013.

I'm not sure if I'd recommend this approach. When displaying the information to the user you need to properly detect that a date range exactly covers a day in order to show "may 25" instead of two over-specific endpoints (which means dealing with daylight savings and so forth).

However, when you're not trying to translate to human, programming with the endpoints is a lot easier than with center+accuracy. You don't end up with lots of cases. That's pretty nice.

share|improve this answer
2  
+1 for the natural approach: it really is a range. –  Thijs van Dien Apr 8 '13 at 20:01

Why not store two dates.

Created_After and Created_Before. The actual semantics being "created on or after" and "created on or before"

So if you know the exact date then Created_After and Created_Before will be the same date.

If you know it was the first week in May 2000 then Created_After = '2000-05-01' and Created_Before = '2000-05-07'.

If you just know May 1999 then the values will be '1999-05-01' and '1999-05-30'.

If it's "Summer of '42" then the values would be '1942-06-01' and '1942-08-31'.

This schema is simple to query with normal SQL, and quite easy for a non technical user to follow.

For instance to find all documents that might have been created in May 2001:

SELECT * FROM DOCTAB WHERE Created_After < '2001-05-31' And Created_Before > 2001-05-01;

Conversely to find all documents that were definitely created in May 2001:

SELECT * FROM DOCTAB WHERE Created_After > '2001-05-01' And Created_Before < 2001-05-31;
share|improve this answer

ISO 8601 date time format comes with duration definition, e.g.

2012-01-01P1M (read: 2012, January 1st, period: 1 month) is what should be “in January 2012”.

I would use this to store the data. You may need a database field of type String to do so. It’s a different topic how to conduct a sensible search on that.

share|improve this answer

If you split it up into multiple columns, you lose the ability to query.

Says who? Here's what you do:

  1. Have 3 columns, Day, Month, Year, each of int type, and a fourth column TheDate of DateTime type.
  2. Have a trigger that uses the 3 columns Day, Month, Year to build TheDate if TheDate is left null but one or more of the Day, Month, Year fields has a value.
  3. Have a trigger that populates Day, Month, Year fields when TheDate is supplied but these fields are not.

So if I do an insert like: insert into thistable (Day, Month, Year) values (-1, 2, 2012); then TheDate will become 2/1/2013 but I will know its really an indeterminate date in 2/2012 because of the -1 in the Day field.

If I insert into thistable (TheDate) values ('2/5/2012'); then Day will be 5, Month will be 2, and Year will be 2012 and because none of them is -1 I will know this is the exact date.

I don't lose the ability to query because the insert/update trigger makes sure my 3 fields (Day, Month, Year) always produce a DateTime value in TheDate which can be queried.

share|improve this answer

Generally, I still store them as dates to general query buisness is still possible even if slightly less accurate.

If it is important to know the accuracy I have in the past either stored an accuracy "window" either as a +/- decimal or as a lookup (day, month, year, etc). In other cases instead of the window I just store the original date value as a string and convert what I can to a datetime, possibly 1978-05-01 00:00:00 and "May 1978" for your given example.

share|improve this answer

Another option would be to store the dates as integers of the form YYYYMMDD.

  • You only know the year is 1951: Store as 19510000
  • You know the month and year is March 1951: Store as 19510300
  • You know the full date is March 14, 1951: Store as 19510314
  • A completely unknown date: Store as 0

Benefits

You can store your fuzzy date in one field instead of two date fields or a date and an accuracy as many of the other answers suggest.

Queries are still easy:

  • all records for the year 1951 - SELECT * FROM table WHERE thedate>=19510000 and thedate<19520000
  • all records for March 1951 - SELECT * FROM table where thedate>=19510300 and thedate<19510400
  • all records for March 14, 1951 - SELECT * FROM table where thedate=19510314

NOTES

  • Your GUI would need a GetDateString(int fuzzyDate) which is pretty easy to implement.
  • Sorting is easy with the int format. You should know that unknown dates will come first. You could reverse this by using 99 for the 'padding' instead of 00 for the month or day.
share|improve this answer

Here is my take on this:

Go from fuzzy date to datetime object (which will fit into a database)

import datetime
import iso8601

def fuzzy_to_datetime(fuzzy):
    flen = len(fuzzy)
    if flen == 4 and fuzzy.isdigit():
        dt = datetime.datetime(year=int(fuzzy), month=1, day=1, microsecond=111111)

    elif flen == 7:
        y, m = fuzzy.split('-')
        dt = datetime.datetime(year=int(y), month=int(m), day=1, microsecond=222222)

    elif flen == 10:
        y, m, d = fuzzy.split('-')
        dt = datetime.datetime(year=int(y), month=int(m), day=int(d), microsecond=333333)

    elif flen >= 19:
        dt = iso8601.parse_date(fuzzy)

    else:
        raise ValueError("Unable to parse fuzzy date: %s" % fuzzy)

    return dt

And then a function that takes the datetime object, and moves it back into a fuzzy date.

def datetime_to_fuzzy(dt):
    ms = str(dt.microsecond)
    flag1 = ms == '111111'
    flag2 = ms == '222222'
    flag3 = ms == '333333'

    is_first = dt.day == 1
    is_jan1 = dt.month == 1 and is_first

    if flag1 and is_jan1:
        return str(dt.year)

    if flag2 and is_first:
        return dt.strftime("%Y-%m")

    if flag3:
        return dt.strftime("%Y-%m-%d")

    return dt.isoformat()

And then a unit test. Did I miss any cases?

if __name__ == '__main__':
    assert fuzzy_to_datetime('2001').isoformat() == '2001-01-01T00:00:00.111111'
    assert fuzzy_to_datetime('1981-05').isoformat() == '1981-05-01T00:00:00.222222'
    assert fuzzy_to_datetime('2012-02-04').isoformat() == '2012-02-04T00:00:00.333333'
    assert fuzzy_to_datetime('2010-11-11T03:12:03Z').isoformat() == '2010-11-11T03:12:03+00:00'

    exact = datetime.datetime(year=2001, month=1, day=1, microsecond=231)
    assert datetime_to_fuzzy(exact) == exact.isoformat()

    assert datetime_to_fuzzy(datetime.datetime(year=2001, month=1, day=1, microsecond=111111)) == '2001'
    assert datetime_to_fuzzy(datetime.datetime(year=2001, month=3, day=1, microsecond=222222)) == '2001-03'
    assert datetime_to_fuzzy(datetime.datetime(year=2001, month=6, day=6, microsecond=333333)) == '2001-06-06'

    assert datetime_to_fuzzy(fuzzy_to_datetime('2002')) == '2002'
    assert datetime_to_fuzzy(fuzzy_to_datetime('2002-05')) == '2002-05'
    assert datetime_to_fuzzy(fuzzy_to_datetime('2002-02-13')) == '2002-02-13'
    assert datetime_to_fuzzy(fuzzy_to_datetime('2010-11-11T03:12:03.293856+00:00')) == '2010-11-11T03:12:03.293856+00:00'

There is a corner case where an event that precisely occurred at 2001-01-01T00:00:00.333333 but the system will interpret as being just "2001", but that seems very unlikely.

share|improve this answer

I work for a publishing company that deals in lots of old books where we often cannot get the exact dates for things. We typically have two fields for a given date entry, the date and a circa boolean:

date date
dateCirca enum('Y', 'N')

We use the date field to indicate the date of some event, or a date that is "close enough" in the case where we don't know the true date. In the event that we do not know the true date, we mark the dateCirca field as Y and give a close enough date, that is marked as the "1st", such as

1st March, 2013  // We don't know the day of the month
1st January, 2013  // We don't know the month/day of the year
1st January, 2000  // We don't know the month/day/year, we only know the century
share|improve this answer

I really like James Anderson's solution - Accurately bounding the dates is the way to get the most flexible query structure. Another way of achieving the same is to use a start, end or even center date plus an interval (available at least in PostgreSQL, Oracle and SQLAlchemy).

share|improve this answer

ISO 8601 also specifies a syntax for "fuzzy dates". February 12th, 2012 at 3pm would be "2012-02-12T15" and February 2012 could be simply "2012-02". This extends nicely using standard lexicographic sorting:

$ (echo "2013-03"; echo "2013-03"; echo "2012-02-12T15"; echo "2012-02"; echo "2011") | sort
2011
2012
2012-02
2012-02-12T15
2013-03
share|improve this answer

In your case you need only year, month and day. Year and month are required, day is optional. I'd use something like that:

year smallint not null,
month smallint not null,
day smallint

Plus is, you can still use indexes very effectively. The (tiny= minus, queires get a bit more "complicated" (longer).

share|improve this answer
1  
But this means that if the fuzziness gobbles up the month part too, this approach fails. –  Anurag Kalia Apr 9 '13 at 11:49
1  
@AnuragKalia - so make the month field nullable. No reason this couldn't be reconfigured at a later date. –  JeffO Apr 9 '13 at 15:10
1  
Have you found such requirement in OPs post or it's just your phantasie? –  Donaudampfschifffreizeitfahrt Apr 10 '13 at 4:29

I would simply store the exact time for normal dates and make the time portion of the fuzzy date generic like 00:00:00. I would then make all the fuzzy dates the 1st of the month.

When you query, you

  1. check for date ranges where the time is also equal to 00:00:00 (fuzzy)
  2. check for date ranges where the time is NOT equal to 00:00:00 (real)
  3. check for date ranges but ignore the time portion (combined)

There are better solutions than this, but I personally hate metadata (data about my data). It just has a habit of getting out of hand after a while.

share|improve this answer

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.