Bug #8376 Adding a Time/Date Range Column Type
Submitted: 8 Feb 2005 22:27 Modified: 16 Sep 2006 10:25
Reporter: Andrew Hanna Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[8 Feb 2005 22:27] Andrew Hanna
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)
[16 Sep 2006 10:25] Valeriy Kravchuk
Thank you for a reasonable feature request. I agree that column type similar to Oracle's (or Informix'es) INTERVAL can be useful in some (rare) cases.