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 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