The traditional comparison operators (=, <, and so on) require that the left and right operands be of the same base type. Excepted equality and inequality, the other comparison operators are not useful in the real world but allow B-tree indexes to be constructed on temporal types. These operators compare the bounding periods (see Section 2.1.5), then the bounding boxes (see Section 4.8) and if those are equal, then the comparison depends on the subtype. For instant values, they compare first the timestamps and if those are equal, compare the values. For instant set and sequence values, they compare the first N instants, where N is the minimum of the number of composing instants of both values. Finally, for sequence set values, they compare the first N sequence values, where N is the minimum of the number of composing sequences of both values.
The equality and inequality operators consider the equivalent representation for different subtypes as shown next.
SELECT tint '1@2001-01-01' = tint '{1@2001-01-01}';
-- true
SELECT tfloat '1.5@2001-01-01' = tfloat '[1.5@2001-01-01]';
-- true
SELECT ttext 'AAA@2001-01-01' = ttext '{[AAA@2001-01-01]}';
-- true
SELECT tgeompoint '{Point(1 1)@2001-01-01, Point(2 2)@2001-01-02}' =
tgeompoint '{[Point(1 1)@2001-01-01], [Point(2 2)@2001-01-02]}';
-- true
SELECT tgeogpoint '[Point(1 1 1)@2001-01-01, Point(2 2 2)@2001-01-02]' =
tgeogpoint '{[Point(1 1 1)@2001-01-01], [Point(2 2 2)@2001-01-02]}';
-- true
Are the temporal values equal?
ttype = ttype: boolean
SELECT tint '[1@2012-01-01, 1@2012-01-04)' = tint '[2@2012-01-03, 2@2012-01-05)'; -- false
Are the temporal values different?
ttype <> ttype: boolean
SELECT tint '[1@2012-01-01, 1@2012-01-04)' <> tint '[2@2012-01-03, 2@2012-01-05)' -- true
Is the first temporal value less than the second one?
ttype < ttype: boolean
SELECT tint '[1@2012-01-01, 1@2012-01-04)' < tint '[2@2012-01-03, 2@2012-01-05)' -- true
Is the first temporal value greater than the second one?
ttype > ttype: boolean
SELECT tint '[1@2012-01-01, 1@2012-01-04)' > tint '[2@2012-01-03, 2@2012-01-05)' -- false
Is the first temporal value less than or equal to the second one?
ttype <= ttype: boolean
SELECT tint '[1@2012-01-01, 1@2012-01-04)' <= tint '[2@2012-01-03, 2@2012-01-05)' -- true
Is the first temporal value greater than or equal to the second one?
ttype >= ttype: boolean
SELECT tint '[1@2012-01-01, 1@2012-01-04)' >= tint '[2@2012-01-03, 2@2012-01-05)' -- false
A possible generalization of the traditional comparison operators (=, <>, <, <=, etc.) to temporal types consists in determining whether the comparison is ever or always true. In this case, the result is a Boolean value. MobilityDB provides operators to test whether the comparison of a temporal value and a value of the base type is ever or always true. These operators are denoted by prefixing the traditional comparison operators with, respectively, ? (ever) and % (always). Some examples are ?=, %<>, or ?<=. Ever/always equality and non-equality are available for all temporal types, while ever/always inequalities are only available for temporal types whose base type has a total order defined, that is, tint, tfloat, or ttext. The ever and always comparisons are inverse operators: for example, ?= is the inverse of %<>, and ?> is the inverse of %<=.
Is the temporal value ever equal to the value?
ttype ?= base: boolean
The function does not take into account whether the bounds are inclusive or not.
SELECT tfloat '[1@2012-01-01, 3@2012-01-04)' ?= 2; -- true SELECT tfloat '[1@2012-01-01, 3@2012-01-04)' ?= 3; -- true SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(2 2)@2012-01-04)' ?= geometry 'Point(1 1)'; -- true
Is the temporal value ever different from the value?
ttype ?<> base: boolean
SELECT tfloat '[1@2012-01-01, 3@2012-01-04)' ?<> 2; -- false SELECT tfloat '[2@2012-01-01, 2@2012-01-04)' ?<> 2; -- true SELECT tgeompoint '[Point(1 1)@2012-01-01, Point(1 1)@2012-01-04)' ?<> geometry 'Point(1 1)'; -- true
Is the temporal value ever less than the value?
tnumber ?< number: boolean
SELECT tfloat '[1@2012-01-01, 4@2012-01-04)' ?< 2;
-- "{[t@2012-01-01, f@2012-01-02, f@2012-01-04)}"
SELECT tint '[2@2012-01-01, 2@2012-01-05)' ?< tfloat '[1@2012-01-03, 3@2012-01-05)';
-- "{[f@2012-01-03, f@2012-01-04], (t@2012-01-04, t@2012-01-05)}"
Is the temporal value ever greater than the value?
tnumber ?> number: boolean
SELECT tint '[1@2012-01-03, 1@2012-01-05)' ?> 1; -- "[f@2012-01-03, f@2012-01-05)"
Is the temporal value ever less than or equal to the value?
tnumber ?<= number: boolean
SELECT tint '[1@2012-01-01, 1@2012-01-05)' ?<= tfloat '{2@2012-01-03, 3@2012-01-04}';
-- "{t@2012-01-03, t@2012-01-04}"
Is the temporal value ever greater than or equal to the value?
tnumber ?>= number: boolean
SELECT 'AAA'::text ?> ttext '{[AAA@2012-01-01, AAA@2012-01-03),
[BBB@2012-01-04, BBB@2012-01-05)}';
-- "{[f@2012-01-01, f@2012-01-03), [t@2012-01-04, t@2012-01-05)}"
Is the temporal value always equal to the value?
ttype %= base: boolean
The function does not take into account whether the bounds are inclusive or not.
SELECT tfloat '[1@2012-01-01, 3@2012-01-04)' %= 2; -- true SELECT tfloat '[1@2012-01-01, 3@2012-01-04)' %= 3; -- true SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(2 2)@2012-01-04)' %= geometry 'Point(1 1)'; -- true
Is the temporal value always different to the value?
ttype %<> base: boolean
SELECT tfloat '[1@2012-01-01, 3@2012-01-04)' %<> 2; -- false SELECT tfloat '[2@2012-01-01, 2@2012-01-04)' %<> 2; -- true SELECT tgeompoint '[Point(1 1)@2012-01-01, Point(1 1)@2012-01-04)' %<> geometry 'Point(1 1)'; -- true
Is the temporal value always less than the value?
tnumber %< number: boolean
SELECT tfloat '[1@2012-01-01, 4@2012-01-04)' %< 2;
-- "{[t@2012-01-01, f@2012-01-02, f@2012-01-04)}"
SELECT tint '[2@2012-01-01, 2@2012-01-05)' %< tfloat '[1@2012-01-03, 3@2012-01-05)';
-- "{[f@2012-01-03, f@2012-01-04], (t@2012-01-04, t@2012-01-05)}"
Is the temporal value always greater than the value?
tnumber %> number: boolean
SELECT tint '[1@2012-01-03, 1@2012-01-05)' %> 1; -- "[f@2012-01-03, f@2012-01-05)"
Is the temporal value always less than or equal to the value?
tnumber %<= number: boolean
SELECT tint '[1@2012-01-01, 1@2012-01-05)' %<= tfloat '{2@2012-01-03, 3@2012-01-04}';
-- "{t@2012-01-03, t@2012-01-04}"
Is the temporal value always greater than or equal to the value?
tnumber %>= number: boolean
SELECT 'AAA'::text %> ttext '{[AAA@2012-01-01, AAA@2012-01-03),
[BBB@2012-01-04, BBB@2012-01-05)}';
-- "{[f@2012-01-01, f@2012-01-03), [t@2012-01-04, t@2012-01-05)}"
Another possible generalization of the traditional comparison operators (=, <>, <, <=, etc.) to temporal types consists in determining whether the comparison is true or false at each instant. In this case, the result is a temporal Boolean. The temporal comparison operators are denoted by prefixing the traditional comparison operators with #. Some examples are #= or #<=. Temporal equality and non-equality are available for all temporal types, while temporal inequalities are only available for temporal types whose base type has a total order defined, that is, tint, tfloat, or ttext.
Temporal equal
{base,ttype} #= {base,ttype}: tbool
SELECT tfloat '[1@2012-01-01, 2@2012-01-04)' #= 3;
-- "{[f@2012-01-01, f@2012-01-04)}"
SELECT tfloat '[1@2012-01-01, 4@2012-01-04)' #= tint '[1@2012-01-01, 1@2012-01-04)';
-- "{[t@2012-01-01], (f@2012-01-01, f@2012-01-04)}"
SELECT tfloat '[1@2012-01-01, 4@2012-01-04)' #= tfloat '[4@2012-01-02, 1@2012-01-05)';
-- "{[f@2012-01-02, t@2012-01-03], (f@2012-01-03, f@2012-01-04)}"
SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(2 2)@2012-01-03)' #=
geometry 'Point(1 1)';
-- "{[f@2012-01-01, t@2012-01-02], (f@2012-01-02, f@2012-01-03)}"
SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(2 2)@2012-01-03)' #=
tgeompoint '[Point(0 2)@2012-01-01, Point(2 0)@2012-01-03)';
-- "{[f@2012-01-01], (t@2012-01-01, t@2012-01-03)}"
Temporal different
{base,ttype} #<> {base,ttype}: tbool
SELECT tfloat '[1@2012-01-01, 4@2012-01-04)' #<> 2;
-- "{[t@2012-01-01, f@2012-01-02], (t@2012-01-02, 2012-01-04)}"
SELECT tfloat '[1@2012-01-01, 4@2012-01-04)' #<> tint '[2@2012-01-02, 2@2012-01-05)';
-- "{[f@2012-01-02], (t@2012-01-02, t@2012-01-04)}"
Temporal less than
{base,torder} #< {base,torder}: tbool
SELECT tfloat '[1@2012-01-01, 4@2012-01-04)' #< 2;
-- "{[t@2012-01-01, f@2012-01-02, f@2012-01-04)}"
SELECT tint '[2@2012-01-01, 2@2012-01-05)' #< tfloat '[1@2012-01-03, 3@2012-01-05)';
-- "{[f@2012-01-03, f@2012-01-04], (t@2012-01-04, t@2012-01-05)}"
Temporal greater than
{base,torder} #> {base,torder}: tbool
SELECT 1 #> tint '[1@2012-01-03, 1@2012-01-05)'; -- "[f@2012-01-03, f@2012-01-05)"
Temporal less than or equal to
{base,torder} #<= {base,torder}: tbool
SELECT tint '[1@2012-01-01, 1@2012-01-05)' #<= tfloat '{2@2012-01-03, 3@2012-01-04}';
-- "{t@2012-01-03, t@2012-01-04}"
Temporal greater than or equal to
{base,torder} #>= {base,torder}: tbool
SELECT 'AAA'::text #> ttext '{[AAA@2012-01-01, AAA@2012-01-03),
[BBB@2012-01-04, BBB@2012-01-05)}';
-- "{[f@2012-01-01, f@2012-01-03), [t@2012-01-04, t@2012-01-05)}"