Datetime comparisons
A date, time, or timestamp value can be compared with another value of the same data type, a datetime constant of the same data type, or with a string representation of a value of that data type. Additionally, a TIMESTAMP WITHOUT TIME ZONE value can be compared with a TIMESTAMP WITH TIME ZONE value.
All comparisons are chronological, which means the further a point in time is from January 1, 0001, the greater the value of that point in time. The time 24:00:00 compares greater than the time 00:00:00.
Comparisons that involve TIME values and string representations of time values always include seconds. If the string representation omits seconds, zero seconds are implied.
Comparisons that involve timestamp values are evaluated according to the following rules:
- When comparing timestamp values with different precisions, the higher precision is used for the comparison and any missing digits for fractional seconds are assumed to be zero.
- When comparing a TIMESTAMP WITH TIME ZONE value to a TIMESTAMP WITHOUT TIME ZONE value, the TIMESTAMP WITHOUT TIME ZONE value is cast to TIMESTAMP WITH TIME ZONE before the comparison is made.
- When comparing two TIMESTAMP WITH TIME ZONE values, the comparison is made using the UTC representations of the values. Two TIMESTAMP WITH TIME ZONE values are considered equal if they represent the same instance in UTC, regardless of the time zone offsets that are stored in the values. For example, '1999-04-15-08.00.00-08:00' (8:00 a.m. Pacific Standard Time) is the same as '1999-04-15-11.00.00-05:00' (11:00 a.m. Eastern Standard Time).
- When comparing a timestamp value with a string representation of a timestamp, the string representation is first converted to a the data type of the timestamp operand. With the except that the converted value has a precision of 12. If the timestamp operand is TIMESTAMP WITHOUT TIME ZONE, the string must not contain a specification of time zone.
- Timestamp comparisons are chronological without regard to representations
that might be considered equivalent. For example, the following predicate
is true:
TIMESTAMP('1990-02-23-00.00.00') > '1990-02-22-24.00.00'
CREATE TABLE TABLE1 (C1 TIMESTAMP WITH TIME ZONE, C2 TIMESTAMP);
A
row is inserted into the table with the following INSERT statement.
The input values are provided by character-string representations
of a timestamp with a time zone.INSERT INTO TABLE1 VALUES ( '2007-11-05-08.00.00-08:00', '2007-11-05-08.00.00');
Assuming
that the implicit time zone is -5:00, the following
SELECT statement will not return any rows. The string representation
of the TIMESTAMP WITHOUT TIME ZONE value is cast to a TIMESTAMP WITH
TIME ZONE value, which results in a timestamp with time zone value
of '2007-11-05-08.00.00-05:00' for column C2. The
comparison predicate is false because the two values are not equal.SELECT 1 FROM TABLE1 WHERE C1 = C2;
SELECT 1 FROM TABLE1 WHERE C2 = :string_hv;