Description:
I think it would be usefule to have a time/date range column type. Although at first this seems very simplistic as you could easily just have a start and end column, the real reason for this is quite specific: The time/date range should be able to be indexed, but more importantly primary or unique indexed. The functionality that I am specifically looking for is this:
1. If a time/date range column is defined in an index as UNIQUE or PRIMARY, then the time/date range cannot overlap with another time/date range of that column.
2. More importantly, if the index has more than one column, than (as per default index behavior), the date/time range is dependant on the other parts of the key.
3. Ability to specify end time/date as NULL, representing forever
4. With ability to have ending time/date as NULL, the ability to sever the NULL on INSERT of new column that cuts into that range (example below)
Therefore, it would be easy to implement a database design where certain objects have a range of time (event start/end time, object activation/expiry, etc). As well, it would be easy to ensure that there is onyl one active object by creating an index on the range.
How to repeat:
-- Feature Request --
Suggested fix:
A few example queries based on a sample table structure:
CREATE TABLE `sample` (
`id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`range` TIMERANGE(14 /* specify range resolution, or other options */) NOT NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
`data` TEXT NOT NULL ,
PRIMARY KEY ( `id` , `range` )
) TYPE = InnoDB;
INSERT INTO `sample` VALUES (1, '0000-00-00 00:00:00->2004-12-31 23:59:59', 'Sample Name', 'Sample Data'); // inserts fine
INSERT INTO `sample` VALUES (1, '0000-00-00 00:00:00->2005-12-31 23:59:59', 'Sample Name', 'Sample Data'); // would fail because range conflicts with PRIMARY KEY range WHERE `id` = 1
INSERT INTO `sample` VALUES (1, '2005-01-01 00:00:00->NULL', 'Sample Name', 'Sample Data'); // inserts fine again
INSERT INTO `sample` VALUES (1, '2005-04-01 00:00:00->NULL', 'April Fools', 'April Fools Data'); // inserts fine again, but previous inserted row would be set to expire at 2005-03-31 23:59:59 (example of feature #4 listed above)
