Example: BETWEEN ... AND Query on ANSI System-Time Table - Analytics Database - Teradata Vantage

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢

To see the nature of the changes to the Fred and Alice rows, you need a query that will return the rows as they existed both before and after the change. You could submit a query with a BETWEEN ... AND temporal qualifier that spans the time of the change. We know from the AS OF query that the change occurred at 2005-05-01.

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME BETWEEN TIMESTAMP'2005-04-30 00:00:00.000001-08:00' AND                         TIMESTAMP'2005-05-02 00:00:00.000001-08:00' 
WHERE ename='Fred' OR ename='Alice' 
ORDER BY ename;

Output:

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

From the results it is clear that from December 1st, 2004 through May 1st, 2005 Alice was in Department 222. From May 1st, 2005 until now, Alice has been in Department 555. Similarly, Fred started in Department 222 and was there from July 1st, 2002 until May 1st, 2005, after which his department also changed to 555, and remains 555 today. Perhaps all the personnel in Department 222 switched departments in 2005, or possibly the department number was changed.