User can't go to the same places twice unless 6 months have pass-CodePudding

I am an intern and this is my first job ever, so sorry for the silly question and the bad english.

CREATE SCHEMA code

CREATE TABLE code.place ( 
   pk_place_id VARCHAR(8), 
   place_name VARCHAR (50),
   CONSTRAINT pk_place_id PRIMARY KEY (pk_place_id)
);


CREATE TABLE code.user ( 
   pk_user_id VARCHAR(3), 
   user_name VARCHAR (50),
   CONSTRAINT pk_user_id PRIMARY KEY (pk_user_id)
);

CREATE TABLE code.visit ( 
   pk_user_id VARCHAR(3), 
   pk_place_id VARCHAR(8),
   data DATE,
   CONSTRAINT pk_user_id FOREIGN KEY (pk_user_id) REFERENCES code.user,
   CONSTRAINT pk_place_id FOREIGN KEY (pk_place_id) REFERENCES code.place
);

I need to make a table that shows where user can go, with the condition that it doesn't show any place that they have been in the last 6 months.

Being honest I am pretty much lost in here, I am an intern my supervisor doesn't know anything about coding (not that I know much of it), just managing people...

UPDATE:

I tried the following code.

SELECT *
    FROM code.visit
    WHERE data >= (curdate() - INTERVAL (1 - DAY(curdate()) DAY) -INTERVAL 6 MONTH;

But nothing happens, it doesn't even say that it is wrong, the only thing i notice is that the terminal goes from looking like:

code=>
code->
code->

To go looking like

code(>
code(>
code(>

AND no other code works until I close and open the terminal.

UPDATE, it was solved it, and the things about the terminal, I just forgot to close the code with ); HAHA, thanks people.

CodePudding user response：

Recommend you check out all of the date/time functions in the docs. For this problem, age() and intervals will be especially useful.

To get a list of place id's where the user CANNOT go:

-- get list of place id's where user CANNOT go
SELECT pk_place_id
  FROM code.visit
 WHERE pk_user_id = '123'
   AND age(data) <= interval '6 months'

To get list of places where the user CAN go, you need a list of ALL of the places minus the places the user cannot go:

 SELECT place_name
   FROM code.place
  WHERE pk_placed_id NOT IN (SELECT pk_place_id
                               FROM code.visit
                              WHERE pk_user_id = '123'
                                AND age(data) <= interval '6 months')

There's probably a clever way you could combine all three tables to give a list of all users and the places they can go, but my SQL skills are not good enough to know how do to that.

CodePudding user response：

I manage so solve it using the following method:

SELECT pk_place_id, pk_user_id, date
    FROM visita_polo
    WHERE EXTRACT(year FROM age(NOW(),data))*12   EXTRACT(month FROM age(now(),data)) > 6;

Thank you for everyones help!!