insert semester in query

Question

In my SQL query there is the column data_solicitacao.

I need to insert in SQL a semester rule where the column "semester 1" or "semester 2" will be automatically filled with the number 1 according to the information of the date data_solicitacao. The completion of the semester must be automatic according to the current date.

Answer 1

You can use a generated column. For example:

create table t (
  registered date,
  semester int generated always as 
    (case when extract(month from registered) <= 6 then 1 else 2 end) stored
);

insert into t (registered) values (date '2022-01-01');
insert into t (registered) values (date '2022-06-30');
insert into t (registered) values (date '2022-07-01');
insert into t (registered) values (date '2022-12-31');

Result:

 registered  semester 
 ----------  -------- 
 2022-01-01  1        
 2022-06-30  1        
 2022-07-01  2        
 2022-12-31  2        

See running example at DB Fiddle.