How can I add the "now" date in a column? I want to achieve the following scenario: if I insert a row today the date column will have the value 25/10/2021 and if tomorrow I'll execute a SELECT statement, I want to see that column updated with the value 26/10/2021 and so on. Is there a way to keep the column updated without me doing anything manually? Thanks.

Later edit: I want to use that column for a PERIOD in a temporal table. Example: PERIOD FOR example_period(some_date, now_date)

CodePudding user response：

That doesn't make any sense. It is as if you'd just

select empno, ename,
  sysdate                     --> this
from employees

every time.

SELECT won't update any column value, so ... why would you store such a date in the first place, if you want to keep it synchronized with sysdate?

But, if you want Oracle to insert sysdate into that column as its default value, you'd then create column as such, e.g.

SQL> create table test
  2    (id      number,
  3     datum   date default sysdate
  4    );

Table created.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> insert into test (id) values (1);

1 row created.

SQL> select * from test;

        ID DATUM
---------- -------------------
         1 25.10.2021 14:05:02

SQL>

But, it would remain the same "forever", unless you actually update its value.

CodePudding user response：

Add a trigger that fill a column with sysdate :

create or replace TRIGGER TRG_FILL_DATE
BEFORE INSERT ON MY_TABLE
FOR EACH ROW
WHEN (NEW.MY_DATE_COLUMN IS NULL)
BEGIN
    SELECT sysdate INTO :NEW.MY_DATE_COLUMN FROM DUAL;
END;

Or as suggested use 'DEFAULT sysdate' on your column.

CodePudding user response：

I am glad we all agree that this makes no sense. But if it makes your boss happy...

Create a view

create myview as
select some_date, trunc(sysdate) as now_date
from mytable;

Or create a computed column

alter mytable add column now_date as trunc(sysdate);

CodePudding user response：

You can use a generated column:

CREATE TABLE salaries (
  id               NUMBER(10,0)
                   GENERATED ALWAYS AS IDENTITY
                   CONSTRAINT salaries__id__pk PRIMARY KEY,
  emp_id           CONSTRAINT salaries__emp_id__fk REFERENCES employees (id)
                   NOT NULL,
  salary           NUMBER(12,2)
                   NOT NULL,
  start_date       DATE
                   NOT NULL,
  end_date         DATE
                   NULL,
  assumed_end_date DATE
                   GENERATED ALWAYS AS (date_or_now(end_date))
                   NOT NULL
);

and create the DETERMINISTIC function (in a slight abuse of how it is supposed to be used):

CREATE FUNCTION date_or_now (dt IN DATE) RETURN DATE DETERMINISTIC
IS
BEGIN
  RETURN COALESCE(dt, SYSDATE);
END;
/

Then if you:

INSERT INTO salaries (emp_id, salary, start_date)
VALUES (1, 123, DATE '2021-01-01');

Then:

SELECT *
FROM   salaries;

The output is:

ID	EMP_ID	SALARY	START_DATE	END_DATE	ASSUMED_END_DATE
1	1	123	2021-01-01 00:00:00		2021-10-25 13:28:08

Then if you do:

UPDATE salaries
SET    END_DATE = DATE '2021-10-01'
WHERE  id = 1;

INSERT INTO salaries (emp_id, salary, start_date)
VALUES (1, 234, DATE '2021-10-01');

Then:

SELECT *
FROM   salaries;

The output is:

ID	EMP_ID	SALARY	START_DATE	END_DATE	ASSUMED_END_DATE
1	1	123	2021-01-01 00:00:00	2021-10-01 00:00:00	2021-10-01 00:00:00
2	1	234	2021-10-01 00:00:00		2021-10-25 13:28:08

db<>fiddle here

CodePudding user response：

In your request comments you have altered your request fundamentally.

One problem: You have an open date range, with a beginning date and an ending date. But when displaying this, you want to substitute the no end" with the current date. This is usually done by storing null (no value) for the date. In a query you can then use COALESCE to replace that with the current date:

SELECT
  empno,
  salary,
  start_date AS first_day,
  COALESCE(end_date, TRUNC(SYSDATE)) AS last_day
FROM salaries;

And for convenience you can make this a view:

CREATE VIEW v_salaries AS SELECT <above query>

Another problem is that you want this null to change when a newer salary information gets added.

Manually you would just

INSERT INTO salaries (empno, salary, start_date, end_date)
VALUES(50, 1200, DATE '2010-12-24', NULL);

UPDATE salaries SET end_date = TRUNC(SYSDATE) - 1
WHERE empno = 50 AND end_date IS NULL;

If you want this update to happen automatically, there are mainly two options:

1. write a procedure for a new salary that does the insert and update
2. write a trigger to perform the update