Datetime comparisons

Start of change 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. End of change

Start of change 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. End of change

Comparisons that involve TIME values and string representations of time values always include seconds. If the string representation omits seconds, zero seconds are implied.

Start of change Comparisons that involve timestamp values are evaluated according to the following rules: End of change

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

Example 1: Table TABLE1 has 2 columns: C1, which is defined as TIMESTAMP WITH TIME ZONE; and C2, which is defined as TIMESTAMP WITHOUT TIME ZONE:

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;

Example 2: When a TIMESTAMP WITHOUT TIME ZONE value is compared with a string representation of a TIMESTAMP WITHOUT TIME ZONE or a TIMESTAMP WITH TIME ZONE value, the string representation is cast to TIMESTAMP WITHOUT TIME ZONE (regardless of whether the string contains a time zone). The comparison is performed using the two TIMESTAMP WITHOUT TIME ZONE values. Assume that string_hv contains a timestamp with time zone value of '2007-11-05-08.00.00-08:00'. The string value is cast to a TIMESTAMP WITHOUT TIME ZONE value of '2007-11-05-08.00.00', which is compared with the value that is stored in column C2. The following SELECT statement returns a single row because a row exists in the table with a timestamp without time zone value of '2007-11-05-08.00.00'.

SELECT 1 FROM TABLE1 WHERE C2 = :string_hv;