Queries: Temporal Table

Database
Fan

Queries: Temporal Table

We tried to replicate the examples provided in the docs in our Teradata environment but the results we obtained is different from what is expected.

Below is the example,

CREATE MULTISET TABLE Prop_Owner (

customer_number INTEGER,

property_number INTEGER,

property_VT PERIOD(DATE) NOT NULL AS VALIDTIME,

property_TT PERIOD (TIMESTAMP(6) WITH TIME ZONE)

NOT NULL AS TRANSACTIONTIME)

PRIMARY INDEX(property_number);

Step 1: INSERT INTO Prop_Owner (customer_number, property_number) VALUES (1, 100);

customer_number

property_number

property_VT

property_TT

1

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:26.390000-05:00','9999-12-31 23:59:59.999999+00:00')

Step 2: UPDATE Prop_Owner SET customer_number = 2  WHERE property_number =100;

After update Obtained Temporal Table:

customer_number

property_number

property_VT

property_TT

2

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:37.880000-05:00', '9999-12-31 23:59:59.999999+00:00')

1

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:26.390000-05:00', '2012-11-01 08:14:37.880000-05:00')

After update Expected temporal table(provided in the PDF docs):

customer_number

property_number

property_VT

property_TT

1

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:26.390000-05:00', '2012-11-01 08:14:37.880000-05:00')

1

100

('2012-11-01', '2012-11-01')

('2012-11-01 08:14:37.880000-05:00', '9999-12-31 23:59:59.999999+00:00')

2

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:37.880000-05:00', '9999-12-31 23:59:59.999999+00:00')

Following are the open items on which we require clarifications,

  1. Would like to confirm whether the obtained temporal table data is valid and correct
  2. Whether it is possible to pass the ValidTime(BUS_EFF_DT) as input variable through Jobs as variables and maintain the history
1 REPLY
Fan

Re: Queries: Temporal Table

We tried to replicate the examples provided in the docs in our Teradata environment but the results we obtained is different from what is expected.

Below is the example,

CREATE MULTISET TABLE Prop_Owner (

customer_number INTEGER,

property_number INTEGER,

property_VT PERIOD(DATE) NOT NULL AS VALIDTIME,

property_TT PERIOD (TIMESTAMP(6) WITH TIME ZONE)

NOT NULL AS TRANSACTIONTIME)

PRIMARY INDEX(property_number);

Step 1: INSERT INTO Prop_Owner (customer_number, property_number) VALUES (1, 100);

customer_number

property_number

property_VT

property_TT

1

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:26.390000-05:00','9999-12-31 23:59:59.999999+00:00')

Step 2: UPDATE Prop_Owner SET customer_number = 2  WHERE property_number =100;

After update Obtained Temporal Table:

customer_number

property_number

property_VT

property_TT

2

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:37.880000-05:00', '9999-12-31 23:59:59.999999+00:00')

1

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:26.390000-05:00', '2012-11-01 08:14:37.880000-05:00')

After update Expected temporal table(provided in the PDF docs):

customer_number

property_number

property_VT

property_TT

1

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:26.390000-05:00', '2012-11-01 08:14:37.880000-05:00')

1

100

('2012-11-01', '2012-11-01')

('2012-11-01 08:14:37.880000-05:00', '9999-12-31 23:59:59.999999+00:00')

2

100

('2012-11-01', '9999-12-31')

('2012-11-01 08:14:37.880000-05:00', '9999-12-31 23:59:59.999999+00:00')

Following are the open items on which we require clarifications,

  1. Would like to confirm whether the obtained temporal table data is valid and correct
  2. Whether it is possible to pass the ValidTime(BUS_EFF_DT) as input variable through Jobs as variables and maintain the history