Use of After, Before, Include in Temporal Database

Question

First of all thank you to read me and try to help me.

I am starting to working with temporal database, exactly with bitemporal database with the next structure:

CREATE TABLE poblat (
  dni VARCHAR2(9), 
  name VARCHAR(12), 
  tiv DATE,
  tfv DATE,
  tit TIMESTAMP,
  tft TIMESTAMP,
  PRIMARY KEY (dni,tiv, tit)
);

I would to know how can i do a query using a clause like after, before or include.

For example i do this:

SELECT nombre, tiv, tfv FROM poblat
WHERE (tiv, tfv) INCLUDE (to_date('31/12/2014'), to_date('31/12/2016'));

But sql developer says that im using an "invalid relational operator".

Thank you for your attention and for your help.

Answer 1

there is no INCLUDE is Oracle's SQL. According to bitemporal db's document http://docs.marklogic.com/guide/temporal/searching#id_78584 , there's two consecutive Allen operators seem complying with include :

aln_equals : x-start = y-start and x-end = y-end aln_contains : x-start < y-start and x-end > y-end

( where X and Y are both periods ) may result in Oracle :

SELECT nombre, tiv, tfv FROM poblat WHERE to_date(tiv,'dd/mm/yyyy') >= to_date('31/12/2014','dd/mm/yyyy') and to_date(tif,'dd/mm/yyyy') <= to_date('31/12/2016','dd/mm/yyyy');

Answer 2

Presumably your WHERE clause is specifying a date range and you are looking for records which fall within that range. If so:

SELECT nombre, tiv, tfv 
FROM poblat
WHERE tiv > =to_date('31/12/2014')  -- start of date range
and  tfv <= to_date('31/12/2016')  -- end of date range
;