I need to track history of party addresses and created a temporal table for this.
CREATE MULTISET TABLE Employee_Addr_Hist (
City varchar (100),
VT PERIOD(DATE) NOT NULL
insert into Employee_Addr_Hist (name,city, Validity) values ( 'John','London' ,period( date '2011-01-01', until_changed) );
Now today(2013-03-19) I received a new row from source saying 'John' moved to 'Paris' on '2012-01-01'.
If I use below Update, CURRENT_DATE is used to close previous record and open new. Which is wrong as John moved to Paris on 2012-01-01.
set City = 'Paris'
where name = 'John'
and end(VT) is until_changed
Can anyone help me in this? I just need to use source date for closing old record and opening new using Temporal features.