Comparisons

Comparisons
Prev	Chapter 6. Temporal Types: Generic Operations (Part 2)	Next

Traditional Comparisons

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 “Comparisons”), then the bounding boxes (see the section called “Comparisons”) 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 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@2001-01-01, 1@2001-01-04)' = tint '[2@2001-01-03, 2@2001-01-05)';
-- false

Are the temporal values different?

ttype <> ttype → boolean

SELECT tint '[1@2001-01-01, 1@2001-01-04)' <> tint '[2@2001-01-03, 2@2001-01-05)';
-- true

Is the first temporal value less than the second one?

ttype < ttype → boolean

SELECT tint '[1@2001-01-01, 1@2001-01-04)' < tint '[2@2001-01-03, 2@2001-01-05)';
-- true

Is the first temporal value greater than the second one?

ttype > ttype → boolean

SELECT tint '[1@2001-01-01, 1@2001-01-04)' > tint '[2@2001-01-03, 2@2001-01-05)';
-- false

Is the first temporal value less than or equal to the second one?

ttype <= ttype → boolean

SELECT tint '[1@2001-01-01, 1@2001-01-04)' <= tint '[2@2001-01-03, 2@2001-01-05)';
-- true

Is the first temporal value greater than or equal to the second one?
ttype >= ttype → boolean
```
SELECT tint '[1@2001-01-01, 1@2001-01-04)' >= tint '[2@2001-01-03, 2@2001-01-05)';
-- false
```

Ever and Always Comparisons

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 or two temporal values 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 and always equality and non-equality are available for all temporal types, while ever and 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 or always equal to the value?

{base,ttype} ?= {base,ttype} → boolean

{base,ttype} %= {base,ttype} → boolean

The ever function does not take into account whether the bounds are inclusive or not.

SELECT tint '[1@2001-01-01, 3@2001-01-04]' ?= 2;
-- false
SELECT tfloat '[1@2001-01-01, 3@2001-01-04)' ?= 2;
-- true
SELECT tgeompoint '[Point(0 0)@2001-01-01, Point(2 2)@2001-01-04]' ?=
  geometry 'Point(2 2)';
-- true
SELECT tgeompoint '[Point(0 0)@2001-01-01, Point(2 2)@2001-01-04)' ?=
  geometry 'Point(2 2)';
-- false

SELECT tfloat '[1@2001-01-01, 1@2001-01-04)' %= 1;
-- true
SELECT tfloat '[1@2001-01-01, 3@2001-01-04)' %= 2;
-- false
SELECT tgeompoint '[Point(0 0)@2001-01-01, Point(2 2)@2001-01-04)' %=
  geometry 'Point(1 1)';
-- false

Is the temporal value ever or always different from the value?

{base,ttype} ?<> {base,ttype} → boolean

{base,ttype} %<> {base,ttype} → boolean

SELECT tfloat '[1@2001-01-01, 3@2001-01-04)' ?<> 2;
-- true
SELECT tfloat '[2@2001-01-01, 2@2001-01-04)' ?<> 2;
-- false
SELECT tgeompoint '[Point(1 1)@2001-01-01, Point(1 1)@2001-01-04)' ?<>
  geometry 'Point(1 1)';
-- false

SELECT tfloat '[1@2001-01-01, 3@2001-01-04)' %<> 2;
-- false
SELECT tfloat '[2@2001-01-01, 2@2001-01-04)' %<> 3;
-- true
SELECT tgeogpoint '[Point(1 1)@2001-01-01, Point(1 1)@2001-01-04)' %<>
  geography 'Point(2 2)';
-- true

Is the temporal value ever or always less than the value?
{base,torder} ?< {base,torder} → boolean
{base,torder} %< {base,torder} → boolean
```
SELECT tint '[1@2001-01-01, 4@2001-01-04]' ?< 2;
-- true
```
```
SELECT tfloat '[1@2001-01-01, 4@2001-01-04)' %< 2;
-- false
```
Is the temporal value ever or always greater than the value?
{base,torder} ?> {base,torder} → boolean
{base,torder} %> {base,torder} → boolean
```
SELECT tint '[1@2001-01-03, 1@2001-01-05]' ?> 0;
-- true
```
```
SELECT tfloat '[1@2001-01-03, 1@2001-01-05)' %> 1;
-- false
```
Is the temporal value ever or always less than or equal to the value?
{base,torder} ?<= {base,torder} → boolean
{base,torder} %<= {base,torder} → boolean
```
SELECT tint '[1@2001-01-01, 1@2001-01-05]' ?<= 2;
-- true
```
```
SELECT tfloat '[1@2001-01-01, 1@2001-01-05)' %<= 4;
-- true
```

Is the temporal value ever or always greater than or equal to the value?

{base,torder} ?>= {base,torder} → boolean

{base,torder} %>= {base,torder} → boolean

SELECT ttext '{[AAA@2001-01-01, AAA@2001-01-03), [BBB@2001-01-04, BBB@2001-01-05)}'
   ?> 'AAA'::text;
-- true

SELECT ttext '{[AAA@2001-01-01, AAA@2001-01-03), [BBB@2001-01-04, BBB@2001-01-05)}'
   %> 'AAA'::text;
-- false

Temporal Comparisons

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@2001-01-01, 2@2001-01-04)' #= 3;
-- {[f@2001-01-01, f@2001-01-04)}
SELECT tfloat '[1@2001-01-01, 4@2001-01-04)' #= tfloat '[1@2001-01-01, 1@2001-01-04)';
-- {[t@2001-01-01], (f@2001-01-01, f@2001-01-04)}
SELECT tfloat '[1@2001-01-01, 4@2001-01-04)' #= tfloat '[4@2001-01-02, 1@2001-01-05)';
-- {[f@2001-01-02, t@2001-01-03], (f@2001-01-03, f@2001-01-04)}
SELECT tgeompoint '[Point(0 0)@2001-01-01, Point(2 2)@2001-01-03)' #=
  geometry 'Point(1 1)';
-- {[f@2001-01-01, t@2001-01-02], (f@2001-01-02, f@2001-01-03)}
SELECT tgeompoint '[Point(0 0)@2001-01-01, Point(2 2)@2001-01-03)' #=
  tgeompoint '{[Point(0 2)@2001-01-01], (Point(0 0)@2001-01-01,
  Point(2 2)@2001-01-03)}';
-- {[f@2001-01-01], (t@2001-01-01, t@2001-01-03)}

Temporal different

{base,ttype} #<> {base,ttype} → tbool

SELECT tfloat '[1@2001-01-01, 4@2001-01-04)' #<> 2;
-- {[t@2001-01-01, f@2001-01-02], (t@2001-01-02, 2001-01-04)}
SELECT tfloat '[1@2001-01-01, 4@2001-01-04)' #<> tfloat '[2@2001-01-02, 2@2001-01-05)';
-- {[f@2001-01-02], (t@2001-01-02, t@2001-01-04)}

Temporal less than

{base,torder} #< {base,torder} → tbool

SELECT tfloat '[1@2001-01-01, 4@2001-01-04)' #< 2;
-- {[t@2001-01-01, f@2001-01-02, f@2001-01-04)}
SELECT tfloat '[2@2001-01-01, 2@2001-01-05)' #< tfloat '[1@2001-01-03, 3@2001-01-05)';
-- {[f@2001-01-03, f@2001-01-04], (t@2001-01-04, t@2001-01-05)}

Temporal greater than

{base,torder} #> {base,torder} → tbool

SELECT 1 #> tint '[1@2001-01-03, 1@2001-01-05)';
-- [f@2001-01-03, f@2001-01-05)

Temporal less than or equal to

{base,torder} #<= {base,torder} → tbool

SELECT tfloat '[1@2001-01-01, 1@2001-01-05)' #<= tfloat '{2@2001-01-03, 3@2001-01-04}';
-- {t@2001-01-03, t@2001-01-04}

Temporal greater than or equal to

{base,torder} #>= {base,torder} → tbool

SELECT 'AAA'::text #> ttext '{[AAA@2001-01-01, AAA@2001-01-03),
  [BBB@2001-01-04, BBB@2001-01-05)}';
-- {[f@2001-01-01, f@2001-01-03), [t@2001-01-04, t@2001-01-05)}

Prev	Up	Next
Restrictions	Home	Bounding Box Operators