Developed 'period'
'period' as time of visibility
First of all, i propose to reduce syntax:
throw out service word 'valid', and to write date without quotes
(it's very long syntax in quotes).
valid period
|
|
period
|
'10 september 2007'
|
10.09.07 -- without quotes
|
Besides that, i propose to understand 'period' exclusively as time,
during which record is visible.
insert into a (a1) values (3.14) period 01.01.07/05.00 till 01.01.07/10.00;
update sys set @datatime=01.01.07/03.00;
select * from a;
-- result: non records, because record is invisible before 05.00
update sys set @datatime=01.01.07/08.00;
select * from a;
-- result: one record
update sys set @datatime=01.01.07/11.00;
select * from a;
-- result: non records, because record is invisible after 10.00
Next two expressions are equivalent
(if to make 'update' instead of 'delete', then old version of record will be visible only in the same period -
from 05.00 to 10.00).
update sys set @datatime=01.01.07/05.00;
insert into a (a1) values (3.14);
update sys set @datatime=01.01.07/10.00;
delete from a;
-- value "3.14" is visible from 05.00 to 10.00
insert into a (a1) values (3.14) period 01.01.07/05.00 till 01.01.07/10.00;
-- value "3.14" is visible from 05.00 to 10.00
It's possible to change not only values of fields, but also time of visibility:
separately
update a period 01.01.07/05.00 till 01.01.07/10.00;
update a period select @t1, @t2 from ... ;
update a period select @ti from ... ; -- datatype "time interval"
or together with changing of fields
update a set @a1=1.00 period 01.01.07/05.00 till 01.01.07/10.00;
update a set @a1=1.00 period select @t1, @t2 from ... ;
update a set @a1=1.00 period select @ti from ... ; -- datatype "time interval"
'period' as analogue of 'where'
It's possible to extract invisible records by force,
if to specify interval of time in request.
select * from a where ... period a inside 01.01.01 till 01.01.10;
select * from a where ... period a inside (select @t1, @t2 from ...);
select * from a where ... period a inside (select @ti from ...);
It's possible to join tables by time of visibility.
select @a1, @b1
from a, b
where ...
period a inside b;
The following operations are possible for period of visibility:
period a < b |
|
a.end < b.begin |
period a <= b |
a.end < b.end |
period a = b |
a.begin=b.begin and a.end=b.end |
period a <> b |
not a.begin=b.begin or not a.end=b.end |
period a >= b |
a.begin > b.begin |
period a > b |
a.begin > b.end |
period a inside b |
a.begin>b.begin and a.end<b.end |
period a include b |
a.begin<b.begin and a.end>b.end |
If interval of time is specified by sub-request,
the service words 'union, intersect, except' in it
should be replaced by words 'combine, cross, reject'
to increase differentiality of request itself and sub-request.
select ...
period a inside select ...
combine -- instead of 'union'
select ...
cross -- instead of 'intersect'
select ...
reject -- instead of 'except'
select ...
union
select ...
;
Dmitry Turin