Range types are MSQL-specific data types for storing interval data. They represent a pair of values indicating the start and the end of an interval.
Ranges be used for storing values that change over some period of time (e.g. location coordinates, speed, etc.) or for values that are naturally best represented as a range (e.g. time periods, range of x-coordinates of a rectangle etc.).
Range values are comparable, which means they can also be sorted.
The ranges are compared first by their start values, and if they're equal, then by their end values.
I.e. comparison of two ranges, e.g. r1 > r2, is equivalent to the comparison of their elements, r1[0] > r2[0] OR (r1[0] = r2[0] AND r1[1] > r2[1]).
Signed and unsigned integer ranges
| Name | Aliases | Storage size (bytes) | Description |
|---|---|---|---|
i8range |
int8range, rint8, ri8 |
2 | Range of i8 values |
i16range |
int16range, rint16, ri16 |
4 | Range of i16 values |
i32range |
int32range, rint32, ri32 |
8 | Range of i32 values |
i64range |
int64range, rint64, ri64 |
16 | Range of i64 values |
u8range |
uint8range, ruint8, ru8 |
2 | Range of u8 values |
u16range |
uint16range, ruint16, ru16 |
4 | Range of u16 values |
u32range |
uint32range, ruint32, ru32 |
8 | Range of u32 values |
u64range |
uint64range, ruint64, ru64 |
16 | Range of u64 values |
f32range |
float32range, rfloat32, rf32 |
8 | Range of f32 values |
f64range |
float64range, rfloat64, rf64 |
16 | Range of f64 values |
Numeric ranges don't assume ordering of the range start and end values, i.e. range end value can be smaller than range start value.
A time range represents an anchored interval of time between two time points.
Time ranges are commonly used to represent events which lasted a certain amount of time, as opposed to those that were instantaneous.
For example, the Fifa World Cup 2022 took place between 20 November and 18 December, which can be represented as a time range tsrange '[2022-11-20, 2022-12-19]' (note that the right bound is exclusive).
This is conceptually different from a time period, which is not anchored, i.e. it only represents a duration of something (e.g. 15 seconds or 10.5 days). Actually, a time period is the duration of a time range.
Time ranges assume that their bounds are ordered ascendingly: their end must be greater (i.e. later) than their start. If that constraint is violated, operations with such disordered time ranges might return counter-intuitive results.
A time range (range of timestamp values) with sub-second precision (less than 1us).
A tsrange value is internally stored as a f64range, i.e. a range of two f64 numbers, each of which represents a fractional number of seconds passed since the Unix epoch.
Other, less common names for this type are rtimestamp, timestamprange and rts.
Disordered timestamps (i.e. timestamp ranges where the start timestamp is larger than the end timestamp) won't cause problems in any of the supported range operations (e.g. INTERSECTS, CONTAINS range predicates),
but in some cases the results might be counter-intuitive. Either way, such timestamp ranges are logically ill-defined and should generally be avoided.
See also
A time range (range of unixepoch values) with a precision of 1 second.
An epochrange value is internally stored as a u32range, i.e. a range of two u32 numbers, each of which represents a number of seconds passed since the Unix epoch.
Other, less common names for this type are runixepoch, unixepochrange and repoch.
The epochrange type is meant to be used only for storage in the cases where millisecond precision
(the precision of regular tsrange range elements) isn't needed, to reduce record size on the disk (epochrange is 8 bytes, tsrange is 16 bytes)
and consequently to improve performance.
See also