PostgreSQL ranges
A feature I'm working on is the ability to cross-reference events, with the idea that users may be interested in,say, what battles took place throughout the war on August 10.
I've created a table of battles that Canada took part in (not an exhaustive list of all WW1 battles) and the time frame for each. So now I can ask for a list of battles that were being fought by Canada on August 10:
SELECT * from battle WHERE time_frame @> '[1915-08-10,1915-08-10]' OR time_frame @> '[1916-08-10,1916-08-10]' OR time_frame @> '[1917-08-10,1917-08-10]' OR time_frame @> '[1918-08-10,1918-08-10]'
Or, perhaps the battles during the winter of 1916/1917:
SELECT * FROM battle WHERE time_frame <@ '[1916-10-01,1917-04-01)'
Or, find all casualties during the war
SELECT str_rank, forename, surname, (SELECT count(id) AS rowcount FROM person_mv2 WHERE date_of_death <@ '[1914-08-04,1921-12-31]') FROM person_mv2 WHERE date_of_death <@ '[1914-08-04,1921-12-31]' GROUP BY forename,surname,str_rank;
Note the parenthetical marks and operators:
defining your range as <@ '[1916-10-01,1917-04-01)'
will find records that include all dates between October 1, 1916 and March 31, 1917 - you use the next day as your upper limit, signifying that you DO NOT WANT TO INCLUDE April 1, 1917 in the query. The <@ means 'contained by'. In the case of an explicit date, like 1918-10-01, you'd use @> (contains).
The documentation is a bit terse and geeky, and it seems to ignore things like use-cases (e.g. above) for dateranges, but it's pretty good overall.