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.

Possible duplicate of [SQL multiple columns in IN clause](https://stackoverflow.com/questions/13027708/sql-multiple-columns-in-in-clause) — Mehdi Ghasri, Nov 25 '17 at 10:17
You could use this function to get number of days overlapped , if that is what you are trying .https://github.com/kaushikjnayak/SQL_PLSQL/blob/56cd7918ce72024d926e6f9ff82385adfcf551e7/Date_overlap.sql — Kaushik Nayak, Nov 25 '17 at 13:20

Barbaros Özhan · Answer 1 · 2017-11-25T18:03:04.970

0

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');

edited Nov 25 '17 at 18:03

answered Nov 25 '17 at 10:12

Barbaros Özhan

59,113
10
31
55

Thank you Barbaros, the query works fine with IN but if i use the clause AFTER or BEFORE sql developer always show the error that says ""invalid relational operator". – Luniz Nov 25 '17 at 10:26
@Chovi you're welcome. AFTER and BEFORE keyword are used especially within a db trigger, but not in a sql statement. Please explicitly edit your question to see where you meet such a mistake. – Barbaros Özhan Nov 25 '17 at 10:30
1

Thank you again! im was so confused. I was trying to use this keyword when i have to use operators like < >. Thank you for your help, I have solved my problem with your help!!!!! – Luniz Nov 25 '17 at 10:31
The Oracle SQL language reference is https://docs.oracle.com/cloud/latest/db112/SQLRF/toc.htm – William Robertson Nov 25 '17 at 11:54
@BarbarosÖzhan `where (tiv, tfv) in (...)` gives me *ORA-00920: invalid relational operator*. Only one item can be specified here. In any case it appears the question is about time ranges not `in` lists. – William Robertson Nov 25 '17 at 12:09
@WilliamRobertson you're right, i've tested and surprised, thank you. – Barbaros Özhan Nov 25 '17 at 18:04

score 0 · Answer 2 · answered Nov 25 '17 at 13:06

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
;

Use of After, Before, Include in Temporal Database

2 Answers2