How to store approximate dates in MySQL?

Question

I need to store dates such as 'Summer 1878' or 'Early June 1923', or even 'Mid-afternoon on a Tuesday in August'. How would you suggest I do this?

I have considered breaking the date and time up into separate (integer) columns, and giving each column an ancillary (integer) column containing a range (0 if exact; NULL if unknown). But I'm sure there's other ways...

Thanks!

Answer 1

Since 'Mid-afternoon on a Tuesday in August' ("A Sunday Afternoon on the Island of La Grande Jatte"?) doesn't specify a year, the only real solution is your table of all date and time components, all nullable.

Other wise, you're conflating your data.

You have two (admittedly related) things here: a human readable string, the date_description, and a range of possible dates.

If you can specify at least a range, you can do this:

create table artwork {
  artwork_id int not null primary key,
  name varchar(80),
  ... other columns
  date_description varchar(80),
  earliest_possible_creation_date datetime
  latest_possible_creation_date datetime
}

insert into artwork( 
  name, 
  date_description, 
  earliest_possible_creation_date, 
  latest_possible_creation_date
) values ( 

  'A Sunday Afternoon on the Island of La Grande Jatte',
  'Mid-afternoon on a Tuesday in August'
  '1884-01-01',
  '1886-12-31'
), (
  'Blonde Woman with Bare Breasts',
  'Summer 1878'
  '1878-05-01',
  '1878-08-31'
), (
   'Paulo on a Donkey',
   'Early June 1923',
   '1923-06-01'
   '1923-06-15'
);

This allows you to display whatever you want, and search for:

select * from artwork 
where @some_date between 
earliest_possible_creation_date and latest_possible_creation_date;

And obviously, "creation date" (the date the artist created the work) is entirely differnet from "date depicted in work", if the latter can be determined at all.