17.10 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)
CREATE MULTISET TABLE employee(
 empno INTEGER,
 ename VARCHAR(50),
 deptno INTEGER,
 jobstart TIMESTAMP WITH TIME ZONE NOT NULL 
   GENERATED ALWAYS AS ROW START,
 jobend TIMESTAMP WITH TIME ZONE NOT NULL AS 
   GENERATED ALWAYS AS ROW END,
 PERIOD FOR SYSTEM_TIME(jobstart,jobend)
 )
 PRIMARY INDEX(empno) WITH SYSTEM VERSIONING;

Assume the table contains the following row:

empno ename  deptno                          jobstart                            jobend

----- ------ ------  --------------------------------  --------------------------------

1025 John      999  2005-02-03 12:12:12.123456+00:00  9999-12-31 23:59:59.999999+00:00

The following SELECT statements would give these results:

SELECT empno,ename(CHAR(6)) FROM employee 
 WHERE END(SYSTEM_TIME) IS UNTIL_CLOSED;

empno ename
------ ------
  1025 John


SELECT empno,ename(CHAR(6)) FROM employee 
 WHERE END(SYSTEM_TIME) IS NOT UNTIL_CLOSED;

*** Query completed. No rows found.