Example: WHERE Clause Results with a Timecode that Precedes Time Zero - Advanced SQL Engine - Teradata Database

It is possible to structure a WHERE clause such that rows are read that have a timecode less than the computed time zero; for example:

CREATE TABLE complexTimeZero(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER )

PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), nonsequenced);

INSERT INTO complexTimeZero VALUES(TIMESTAMP '2013-01-06 10:00:24.000000', 1, 55, 43);

INSERT INTO complexTimeZero VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 56, 44);

SELECT AVG(TEMPERATURE)

FROM complexTimeZero

WHERE TD_TIMECODE >= TIMESTAMP'2014-01-01 00:00:00' OR buoyid=1

GROUP BY TIME(MINUTES(10));

Based on the condition on TD_TIMECODE, time zero is equal to TIMESTAMP'2014-01-01 00:00:00'.

However, the OR condition will also allow a row with TD_TIMECODE equal to TIMESTAMP '2013-01-06 10:00:24.000000' to be included in the aggregation. In all cases where the timecode is less than time zero, an error is returned indicating the timecode precedes time zero.