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?

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.

  • 15
    Is it important to differentiate "unknown day in May 1978" with "May 1st, 1978"?
    – user40980
    Commented Apr 8, 2013 at 15:18
  • 6
    @MichaelT: yes, it is important to differentiate.
    – nbv4
    Commented Apr 8, 2013 at 15:19
  • 6
    – psr
    Commented Apr 8, 2013 at 18:13
  • 6
    @aslum: Most database systems will reject that as an invalid date value
    – nbv4
    Commented Apr 8, 2013 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? Commented Apr 8, 2013 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.

  • 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. Commented Apr 8, 2013 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. Commented Apr 8, 2013 at 21:21
  • 62
    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.
    – user40980
    Commented Apr 8, 2013 at 21:45
  • 1
    You should maybe submit that as an answer, MichaelT
    – Supr
    Commented Apr 9, 2013 at 9:45
  • 1
    +1: Another nice thing about this solution is you can then add display logic based on the value of date_created_accuracy field. You can show "May 1980" or just "1980" in the results or the UI if that's as accurate as the field indicates.
    – Kyralessa
    Commented Jun 26, 2015 at 15:31

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.
  • Min and max date fields were my first thought as well. Commented Apr 8, 2013 at 15:30
  • 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
    Commented Apr 8, 2013 at 16:47
  • 4
    +1 for min date and max date fields. I think that is the most flexible, yet precise and easy to use solution.
    – Supr
    Commented Apr 9, 2013 at 11:13
  • 1
    I was antagonistic to this idea at first. But realizing it is the most flexible approach, I vote for this. Commented Apr 9, 2013 at 11:47
  • It's only natural. You're describing not so much a fuzzy date but a timeframe.....which has a start and an end.
    – Pieter B
    Commented Apr 9, 2013 at 14:53

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.

  • 1
    +1 - I was working on formulating an answer with the double date approach. Your answer got here first.
    – user40980
    Commented Apr 8, 2013 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
    Commented Apr 8, 2013 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. Commented Apr 8, 2013 at 19:58
  • 1
    +1 for being able to denote arbitrary granularity without an explosion of enum values. Commented Apr 8, 2013 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

  • Queries become really hairy when you do that.
    – Blrfl
    Commented Apr 8, 2013 at 16:34
  • 3
    This has difficulty with data that isn't on a clean month boundary such as "Q2 1991" and "Winter 1978-1979".
    – user40980
    Commented Apr 8, 2013 at 17:37
  • 1
    OP wants some way to denote that this date is only accurate to the month. Commented Apr 8, 2013 at 18:32
  • 7
    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
    Commented Apr 9, 2013 at 10:57
  • @Konerak Semantically yes. But as the majority of dates are accurate only the special cases need to be identified and using NULL here as default. Commented Apr 9, 2013 at 12:20

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.

  • Actually, it doesn't need to be so tricky to determine how to present a range if the range is always stored as UTC. As UTC timestamps, every day, week, month, year -- even seasons and quarters -- will have two constant, global, distinct and easily determinable numbers representing the start and end of the period. The logic simply becomes a few if-statements to see if the two dates are at the beginning and end of some type of period. No complicated math or time zone stuff needed :)
    – Supr
    Commented Apr 9, 2013 at 11:50
  • @Supr Determining if a particular second is on the border of a particular human period is, in itself, a difficult problem. Especially over the long term, with the Earth's rotation slowing down and unending small changes to the human definition of local time. Commented Apr 9, 2013 at 16:34

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;
  • 1
    I think this is the most elegant solution.
    – Pieter B
    Commented Apr 9, 2013 at 11:20
  • This is the same as superM and Strilanc's answers. +1 though for explaining more clearly and showing how simple it would be to query.
    – Supr
    Commented Apr 9, 2013 at 12:08
  • Also like this answer!
    – smoothware
    Commented May 20, 2021 at 21:14

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.

  • +1 for the idea but -1 for not using a date field for the reason how to search and/or find
    – mmmmmm
    Commented Apr 9, 2013 at 14:43
  • Depends on the database. However this can be base to expansion, but the question is: Is the document in the result set if you search, in this case, all documents newer than the 12th of January, or is it not? It’s not trivial. Here, the question was how to store fuzzy dates. Commented Apr 9, 2013 at 14:49

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.


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.


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


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


  • 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.
  • How do you represent the fuzzy date of "winter of 1941-1942"? It could be December 1941, or January 1942.
    – user40980
    Commented Mar 8, 2014 at 14:43
  • 1
    Your question is related to a general solution case. The original question does not list this as a problem. Based on the posted question, sometimes the full date is known, sometime only the year and month, and sometimes only the year. No issue of a fuzzy date range is mentioned as a requirement. I would agree you need two dates if you needed to solve this problem (although, storing the range as two "fuzzy date ints" could provide more flexibility thant storing two "hard" dates).
    – Rick
    Commented Mar 9, 2014 at 15:30

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

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)

        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.


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


There are many possible representations, and thus database schemas, for storing fuzzy date-times (or even just fuzzy dates):

  1. Date-time and code indicating its precision or accuracy
  2. Date-time and interval where there are several possibilities for representing an interval:
    1. Represent all intervals as an integer (or other numeric) quantity of some fixed unit, e.g. days, minutes, nanoseconds.
    2. Represent an interval as both an integer (or other numeric) quantity and a code indicating its units.
  3. Start and end date-times
  4. String
  5. Probability distribution:
    1. Decimal or floating-point quantities for the parameters that specify a specific distribution in a particular family, e.g. mean and standard deviation of a normal distribution.
    2. Probability distribution function, e.g. as a (lookup) code (potentially with parameters of specific values), or as an expression in a sufficiently expressive language, format, or representation.

[1], [2], and [3] are all (implicitly) uniform intervals, i.e. a set of (equally) possible points in time.

[4] is the most expressive, i.e. when allowing any possible (or at least arbitrarily long) written language sentences or phrases. But it's also the hardest to work with. In the limit, human-level AI would be required to handle arbitrary values. Practically, the range of possible values would need to be restricted severely, and alternative 'structured' values would probably be preferred for many operations, e.g. sorting, searching.

[5] is probably the most general compact representation that's (somewhat) practical.

Uniform Intervals

Uniform intervals are the simplest compact way to represent a set of (possible) date-time values.

For [1], portions of the date-time value are ignored, i.e. the portions corresponding to units finer than the indicated precision or accuracy; otherwise this is equivalent to [2] and the precision/accuracy code is equivalent to an interval with the same units (and an implied quantity of 1).

[2] and [3] are expressively equivalent. [1] is strictly less expressive than either as there are effective intervals that cannot be represented by [1], ex. a fuzzy date-time equivalent to a 12 hour interval that spans a date boundary.

[1] is easier for users to input than any other representation and should generally require (at least slightly) less typing. If date-times can be input in various text representations, e.g. "2013", "2014-3", "2015-5-2", "7/30/2016 11p", "2016-07-31 18:15", the precision or accuracy could also be inferred automatically from the input.

The accuracy or precision of [1] is also easiest to convert to a form to be conveyed to users, e.g. '2015-5 with month accuracy' to "May 2015", versus "May 13th 2015 2p, plus or minus 13.5 days" (tho note that the latter can't be represented by [1] anyways).


Practically, string values will need to be converted to other representations for querying, sorting, or otherwise comparing multiple values. So while any written natural (human) language is strictly more expressive than [1], [2], [3], or [5], we don't yet have the means of handling much beyond standard text representations or formats. Given that, this is probably the least useful representation by itself.

One advantage of this representation tho is that values should, in practice, be presentable to users as-is and not require transformation to be easily understandable.

Probability Distributions

Probability distributions generalize the uniform interval representations [1], [2], [3], and (arguably) are equivalent to the (general) string representation [4].

One advantage of probability distributions over strings is that the former is unambiguous.

[5-1] would be appropriate for values that (mostly) conform to an existing distribution, e.g. a date-time value output from a device for which measurements are known (or thought) to conform to a specific distribution.

[5-2] is probably the best (somewhat) practical way to compactly represent arbitrary 'fuzzy datetime' values. Of course the computability of the specific probability distributions used matters and there are definitely interesting (and perhaps impossible) problems to be solved when querying, sorting, or comparing different values, but a lot of this is probably already known or solved somewhere in the existing mathematical and statistical literature so this definitely stands as an extremely general and un-ambiguous representation.


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).


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.

  • 3
    how would this deal with real date having time 00:00:00?
    – gnat
    Commented Apr 9, 2013 at 9:46
  • While it is theoretically possible to add a real date with that time, it won't happen. I have seen tables with millions of rows and not a single one of them had a datetime value where the time was 00:00:00. Pragmatism trumps convention. Commented Apr 9, 2013 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.