$TD_TIMECODE_RANGE | Teradata Vantage - $TD_TIMECODE_RANGE - Advanced SQL Engine

$TD_TIMECODE_RANGE is a virtual system column that specifies the range of time over which an aggregate result is computed. Its data type is period(timestamp(6) with time zone). The timecode range column can store a time range such as: ('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:30:00.000000+00:00'). It can be used in the SELECT and ORDER BY clauses.

To retrieve and use the column, enter $TD_TIMECODE_RANGE in the SELECT or ORDER BY clause:

The default TITLE is TIMECODE_RANGE.
The user may change the name of the default title with the AS clause.
The data type of the column is the Period Data Type (PDT).
Use the $TD_TIMECODE_RANGE name label in the same manner as you would use a PDT name label:
- It can be referenced directly
- It can launch any of the PDT associated FUNCTIONS ... BEGIN($TD_TIMECODE_RANGE), END($TD_TIMECODE_RANGE)
- You can use it in any expression (including passing it into functions)
- You can use it in an ORDER BY clause similar to other PDT columns

$TD_TIMECODE_RANGE Values

The returned values follow the PDT convention, so the specified range includes the beginning time but does not include the ending time. The beginning time of the range associated with a particular GROUP BY TIME interval number is:

For a single range: the beginning time is the starting time of the range.
For multiple ranges separated by ORs: the beginning time is the earliest starting time among all the ranges specified.

For more information, see GROUP BY TIME - Time Zero Value.

In the following example there is just a single time range specified as part of the query:

SELECT ... WHERE TD_TIMECODE BETWEEN TIMESTAMP ’2016-10-01 08:00:00’ AND TIMESTAMP ’2016-10-01 09:00:00’ GROUP BY TIME(MINUTES(15))

Where:

GROUP BY TIME time zero value is 2016-10-01 08:00:00
GROUP BY TIME interval number is 15
GROUP BY TIME interval duration is MINUTES

For more information, see GROUP BY TIME - Time Zero Value.

This produces results with the numbering 1,2,3,4:

GROUP BY TIME (MINUTES(15))	TIMECODE_RANGE
1	'2016-10-01 08:00:00', '2016-10-01 08:15:00'
2	'2016-10-01 08:15:00', '2016-10-01 08:30:00'
3	'2016-10-01 08:30:00', '2016-10-01 08:45:00'
4	'2016-10-01 08:45:00', '2016-10-01 09:00:00'

The example snippet shows multiple time ranges specified as part of the query:

SELECT ... WHERE TD_TIMECODE BETWEEN TIMESTAMP ’2016-10-01 08:00:00’ AND TIMESTAMP ’2016-10-01 09:00:00’
OR TIMECODE BETWEEN TIMESTAMP ’2016-10-01 12:15:00’ AND TIMESTAMP ’2016-10-01 13:30:00’
GROUP BY TIME(MINUTES(15))

Where (in this case there were multiple ranges):

GROUP BY TIME time zero value is '2016-10-01 08:00:00' because it is the earliest time zero among both of the ranges
GROUP BY TIME interval number is 15
GROUP BY TIME interval number duration is MINUTES

This produces the following results:

GROUP BY TIME (MINUTES(15))	TIMECODE_RANGE
1	'2016-10-01 08:00:00', '2016-10-01 08:15:00'
2	'2016-10-01 08:15:00', '2016-10-01 08:30:00'
3	'2016-10-01 08:30:00', '2016-10-01 08:45:00'
4	'2016-10-01 08:45:00', '2016-10-01 09:00:00'
18	'2016-10-01 12:15:00', '2016-10-01 12:30:00'
19	'2016-10-01 12:30:00', '2016-10-01 12:45:00'
20	'2016-10-01 12:45:00', '2016-10-01 13:00:00'
21	'2016-10-01 13:00:00', '2016-10-01 13:15:00'
22	'2016-10-01 13:15:00', '2016-10-01 13:30:00'

$TD_TIMECODE_RANGE Values and System Time Zone Settings

$TD_TIMECODE_RANGE values are based on system time zone settings specified by the tdlocaledef utility or the following DBS Control Fields:

16. System TimeZone Hour
17. System TimeZone Minute
18. System TimeZone String
57. TimeDateWZControl

Consider the following table, data, and DBS Control settings:

CREATE TABLE ocean_buoy (BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), nonsequenced);

INSERT INTO ocean_buoy VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);

DBS Control Fields default system time zone settings:

16. System TimeZone Hour = 0
17. System TimeZone Minute = 0
57. TimeDateWZControl = 2

The following query:

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, AVG(temperature)
FROM ocean_buoy
GROUP BY TIME(HOURS(1)) ORDER BY 1;

Returns this result:

TIMECODE_RANGE	GROUP BY TIME(HOURS(1))	Average(TEMPERATURE)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 09:00:00.000000+00:00')	17673	10

If the DBS Control Fields settings are the following:

16. System TimeZone Hour = 8
17. System TimeZone Minute = 0
57. TimeDateWZControl = 2

The following query:

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, AVG(temperature)
FROM ocean_buoy
GROUP BY TIME(HOURS(1)) ORDER BY 1;

Returns this result:

TIMECODE_RANGE	GROUP BY TIME(HOURS(1))	Average(TEMPERATURE)
('2014-01-06 16:00:00.000000+08:00', '2014-01-06 17:00:00.000000+08:00')	17673	10

If the DBS Control Fields settings are the following:

16. System TimeZone Hour = 5
17. System TimeZone Minute = 0
57. TimeDateWZControl = 3

The following query:

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, AVG(temperature)
FROM ocean_buoy
GROUP BY TIME(HOURS(1)) ORDER BY 1;

Returns this result:

TIMECODE_RANGE	GROUP BY TIME(HOURS(1))	Average(TEMPERATURE)
('2014-01-06 08:00:00.000000+05:00', '2014-01-06 09:00:00.000000+05:00')	17668	10

If you use the tdlocaledef utility to set the TimeZoneString to "America Pacific", then the following query:

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, AVG(temperature)
FROM ocean_buoy
GROUP BY TIME(HOURS(1)) ORDER BY 1;

Returns this result:

TIMECODE_RANGE	GROUP BY TIME(HOURS(1))
('2014-01-06 00:00:00.000000-08:00', '2014-01-06 01:00:00.000000-08:00')	17673

Ragged $TD_TIMECODE_RANGE Values

When any timebucket of a range is specified so that it does not complete an entire timebucket (based on the duration specified) the resulting $TD_TIMECODE_RANGE value reflects this discrepancy as shown in this example:

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*)
FROM OCEAN_BUOYS
WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 08:30:00' OR
TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 10:02:00' AND TIMESTAMP '2014-01-06 10:15:00'
GROUP BY TIME (MINUTES(10) AND BUOYID)
ORDER BY 2, 3;

Result:

TIMECODE_RANGE	GROUP BY TIME (MINUTES(10))	BUOYID	Average (TEMPERATURE)	Count (*)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00')	1	0	54	3
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00')	2	0	55	2
('2014-01-06 10:02:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00')	13	44	54.25	4
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:15:00.000000+00:00')	14	44	43	1

$TD_TIMECODE_RANGE | Teradata Vantage - $TD_TIMECODE_RANGE - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

$TD_TIMECODE_RANGE Values

$TD_TIMECODE_RANGE Values and System Time Zone Settings

Ragged $TD_TIMECODE_RANGE Values