Calculates the time difference, or DELTA_T, between a starting and an ending event. The calculation is performed against a time-ordered time series data set.
To invoke the DELTA_T function, use the GROUP BY TIME clause.
Return Value
DELTA_T returns a PERIOD(TIMESTAMP WITH TIME ZONE) type composed of the start and end timecode of each start-end pair.
Use the PERIOD DATA TYPES functions to inspect the results, as described in "Period Functions and Operators" in Teradata Vantage™ - SQL Date and Time Functions and Expressions, B035-1211.
- If the current source data meets the start condition, the current timecode is saved as the start time.
- If the current source data meets the end condition, and a saved start timecode already exists, the start timecode is saved with the end timecode encountered as a result pair.
The processing algorithm implies that multiple results may be found in each group.
If no start-end pair is encountered, no result row is returned.
Any result of DELTA_T which has a delta of < 1 microsecond (including a delta of 0, in the case of a result which comes from a single point in time) is automatically rounded to 1 microsecond. This is strictly enforced to match Period data type semantics which dictate that a starting and ending bound of a Period type may not be equivalent. The smallest granularity supported in Vantage is the microsecond, so these results are rounded accordingly.
Usage Notes
You can only explicitly specify DELTA_T in the SELECT list one time, but can further alias DELTA_T in the SELECT list, ORDER BY and HAVING clauses.
- The time between a user calling a customer service line and reaching a customer service representative.
- The time between minimum and maximum observations (such as temperature, wind speed, or salinity).
- The time between reporting a software defect and its resolution.
Understanding how long a customer waits to speak to a service representative at different times during the day, or on different days during the week, helps companies staff their customer service department according to actual needs. The companies can spend money more efficiently and provide better service to customers.
The DELTA_T function cannot be combined with any other functions.
DELTA_T is the only aggregate function used with GROUP BY TIME(*).
- $TD_GROUP_BY_TIME: Always has a value of 1, since there is only one timebucket.
- $TD_TIMECODE_RANGE: Composed of the first and last timecode values read for the group.
Note that the data being evaluated in the WHERE clauses (for example, the minimum and maximum temperature observation) must belong to the timecode value present in the same row of data. This is the expected behavior. However, this assumption can be violated when joining multiple tables together. It is possible to construct a query where the result of a join causes specific data points (for example, a temperature reading) to be present in a data row with a timecode that is not indicative of when that data point occurred. In such a scenario, it is highly likely that the results are not as expected, or are misleading. Vantage does not detect these types of queries, so you must make sure that you preserve the correlation between data points and timecodes.