Comparisons of dates and times

The table below summarizes the conversions that are implicit when comparing certain data types with date, time, or date-time
data types.

Data type

Data type

Conversion

CHAR

DATE

CHAR cast to TIMESTAMP; DATE cast to TIMESTAMP

CHAR

TIME

CHAR cast to TIME

CHAR

TIMESTAMP

CHAR cast to TIMESTAMP

CHAR

TIMESTAMP WITH TIME ZONE

CHAR cast to TIMESTAMP WITH TIME ZONE

DATE

TIME

illegal

DATE

TIMESTAMP

DATE cast to TIMESTAMP

DATE

TIMESTAMP WITH TIME ZONE

DATE cast to TIMESTAMP WITH TIME ZONE

DATE

SMALLINT, INTEGER, BIGINT, and NUMERIC

SMALLINT, INTEGER, BIGINT, and NUMERIC value treated as a date string and cast to TIMESTAMP; DATE cast to TIMESTAMP

DATE

REAL, FLOAT, and DOUBLE

REAL, FLOAT, and DOUBLE treated as a number of days since 0000-02-29 and cast to TIMESTAMP; DATE cast to TIMESTAMP

TIME

TIMESTAMP

TIMESTAMP cast to TIME

TIME

TIMESTAMP WITH TIME ZONE

illegal

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP cast to TIMESTAMP WITH TIME ZONE

TIMESTAMP

SMALLINT, INTEGER, BIGINT, and NUMERIC

SMALLINT, INTEGER, BIGINT, and NUMERIC value treated as a date string and cast to TIMESTAMP

TIMESTAMP

REAL, FLOAT, and DOUBLE

REAL, FLOAT, and DOUBLE treated as a number of days since 0000-02-29 and cast to TIMESTAMP

The following points expand on the information presented in the table above.

Only values of type TIME, TIMESTAMP, and CHAR can be compared to a value of type TIME. Comparison with values of other data
types results in a conversion error. When comparing a time value and a value of another type, the comparison data type is
TIME.

When comparing a TIMESTAMP WITH TIME ZONE value to a DATE value, the comparison data type is TIMESTAMP WITH TIME ZONE.

When a time value is cast to a TIMESTAMP, the result is formed by combining the current date with the time value.

Exact numeric values of type SMALLINT, INTEGER, BIGINT, and NUMERIC can be converted to date values. The conversion is performed
by treating the number as a string. For example, the integer value 20100401 represents the first day of April in 2010.

Approximate numeric values of type REAL, FLOAT, and DOUBLE can be converted to dates by treating the number as the number
of days since the fictional date 0000-02-29. For example, 307 represents 0001-01-01 and 734169 represents 2010-04-01.