MySQL Bugs: #20844: interval datatype

Bug #20844	interval datatype
Submitted:	4 Jul 2006 8:51	Modified:	4 Jul 2006 9:16
Reporter:	Mircea LUTIC	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
I suggest adding the interval datatype to mysql as it is in Informix.
There already is the interval syntax in date/time functions.
It should be natural and easy to add the interval datatype to the available column datatypes.

Thank you,
Mircea.

How to repeat:
I suggest adding the interval datatype to mysql as it is in Informix.
There already is the interval syntax in date/time functions.
It should be natural and easy to add the interval datatype to the available column datatypes.

Thank you,
Mircea.

Here is an excerpt from the informix documentation:

The INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes: year-month intervals and day-time intervals. A year-month interval can represent a span of years and months, and a day-time interval can represent a span of days, hours, minutes, seconds, and fractions of a second.

An INTERVAL value is always composed of one value or a series of values that represents time units. Within a data-definition statement such as CREATE TABLE or ALTER TABLE that defines the precision of an INTERVAL data type, the qualifiers must have the following format:

INTERVAL largest_qualifier(n) TO smallest_qualifier
Here the largest_qualifier and smallest_qualifier keywords are taken from one of the two INTERVAL classes, as shown in Table 8.

If SECOND (or a larger time unit) is the largest_qualifier, the declaration of an INTERVAL data type can optionally specify n, the precision of the largest time unit (for n ranging from 1 to 9); this is not a feature of DATETIME data types.

If smallest_qualifier is FRACTION, you can also specify a scale in the range from 1 to 5. For FRACTION TO FRACTION qualifiers, the upper limit of n is 5, rather than 9. There are two incommensurable classes of INTERVAL data types:

Those with a smallest_qualifier larger than DAY
Those with a largest_qualifier smaller than MONTH

Table 8. Interval Classes Interval Class Time Units Valid Entry
YEAR-MONTH
INTERVAL YEAR A number of years
MONTH A number of months
DAY-TIME INTERVAL DAY A number of days
HOUR A number of hours
MINUTE A number of minutes
SECOND A number of seconds
FRACTION A decimal fraction of a second, with up to 5 digits. The default scale is 3 digits (thousandth of a second). To specify a non-default scale, write FRACTION(n), where 1 ≤ n ≤ 5.

As with DATETIME data types, you can define an INTERVAL to include only the subset of time units that you need. But because the construct of "month" (as used in calendar dates) is not a time unit that has a fixed number of days, a single INTERVAL value cannot combine months and days; arithmetic that involves operands of the two different INTERVAL classes is not supported.

A value entered into an INTERVAL column need not include the full range of time units that were specified in the data-type declaration of the column. For example, you can enter a value of HOUR TO SECOND precision into a column defined as DAY TO SECOND. A value must always consist, however, of contiguous time units. In the previous example, you cannot enter only the HOUR and SECOND values; you must also include MINUTE values.

A valid INTERVAL literal contains the INTERVAL keyword, the values to be entered, and the field qualifiers. (See the discussion of literal intervals in the IBM Informix: Guide to SQL Syntax.) When a value contains only one field, the largest and smallest fields are the same.

When you enter a value in an INTERVAL column, you must specify the largest and smallest fields in the value, just as you do for DATETIME values. In addition, you can optionally specify the precision of the first field (and the scale of the last field if it is a FRACTION). If the largest and smallest field qualifiers are both FRACTION, you can specify only the scale in the last field.

Acceptable qualifiers for the largest and smallest fields are identical to the list of INTERVAL fields that Table 8 displays.

If you use the DB–Access TABLE menu, but you specify no INTERVAL field qualifiers, then a default INTERVAL qualifier, YEAR TO YEAR, is assigned.

The largest_qualifier in an INTERVAL value can be up to nine digits (except for FRACTION, which cannot be more than five digits), but if the value that you want to enter is greater than the default number of digits allowed for that field, you must explicitly identify the number of significant digits in the value that you enter. For example, to define an INTERVAL of DAY TO HOUR that can store up to 999 days, you could specify it the following way:

INTERVAL DAY(3) TO HOUR
INTERVAL literals use the same delimiters as DATETIME literals (except that MONTH and DAY time units are not valid within the same INTERVAL value). Table 9 shows the INTERVAL delimiters.

Table 9. INTERVAL Delimiters Delimiter Placement in an INTERVAL Literal
Hyphen Between the YEAR and MONTH portions of the value
Blank space Between the DAY and HOUR portions of the value
Colon Between the HOUR, MINUTE, and SECOND portions of the value
Decimal point Between the SECOND and FRACTION portions of the value

You can also enter INTERVAL values as character strings. The character string must include information for the same time units that were specified in the data-type declaration for the column. The INSERT statement in the following example shows an INTERVAL value entered as a character string:

INSERT INTO manufact (manu_code, manu_name, lead_time)
VALUES ('BRO', 'Ball-Racquet Originals', '160')
Because the lead_time column is defined as INTERVAL DAY(3) TO DAY, this INTERVAL value requires only one field, the span of days required for lead time. If the character string does not contain information for all fields (or adds additional fields), the database server returns an error. For additional information on entering INTERVAL values as character strings, see the IBM Informix: Guide to SQL Syntax.

By default, all fields of an INTERVAL column are two-digit numbers, except for the year and fraction fields. The year field is stored as four digits. The fraction field requires n digits where 1 ≤ n ≤ 5, rounded up to an even number. You can use the following formula (rounded up to a whole number of bytes) to calculate the number of bytes required for an INTERVAL value:

(total number of digits for all fields)/2 + 1
For example, INTERVAL YEAR TO MONTH requires six digits (four for year and two for month), and requires 4, or (6/2) + 1, bytes of storage.

For information on using INTERVAL data in arithmetic and relational operations, see Manipulating DATE with DATETIME and INTERVAL Values. For information on using INTERVAL as a constant expression, see the description of the INTERVAL Field Qualifier in the IBM Informix: Guide to SQL Syntax.

Informix documentation link:
http://pipin.tmd.ns.ac.yu/databases/informix/4365.pdf

Thank you for a reasonable feature request.

I was just looking for this datatype myself so it would be nice to have.  The time datatype only allows 34 days so it won't suffice for my purposes.  I guess I'll just use two ints for days and hours for now.

We need this feature added for an Informix to MySql port.  I do not understand why this has set for 4 years with out a comment?

qIt's been several years now, I guess this is not going to be added.