Comparison Operators
Prev Chapter 5. Manipulating Temporal Types Next

Comparison Operators

Traditional Comparison Operators

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 the section called “Comparison Operators”), then the bounding boxes (see the section called “Comparison Operators”) 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

Ever and Always Comparison Operators

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)}"

Temporal Comparison Operators

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)}"
Prev Up Next
Restriction Functions Home Mathematical Functions and Operators