current_timestamp interval 4 day in into or other expression-CodePudding

Hello im haveing this code and i want to add data in the table also the current day 4 day interval, i tried with the beginig of the table creation dident work or i need an expresion thas gonna change the date value to the current_timestamp interval 4 day.

CREATE TABLE IF NOT EXISTS `Spital`.`Stoc General` (
  `ID Produs` INT ,
  `Denumire` VARCHAR(45) NOT NULL,
  `Cantitate` INT NULL,
  PRIMARY KEY (`ID Produs`))
ENGINE = InnoDB;

Select*From `Spital`.`Stoc General`;
INSERT INTO `spital`.`stoc general` (`ID Produs`, `Denumire`, `Cantitate`) VALUES ('1', 'Clabax', '20');
INSERT INTO `spital`.`stoc general` (`ID Produs`, `Denumire`, `Cantitate`) VALUES ('2', 'Betadina', '15');
INSERT INTO `spital`.`stoc general` (`ID Produs`, `Denumire`, `Cantitate`) VALUES ('3', 'Paracetamo', '4');
INSERT INTO `spital`.`stoc general` (`ID Produs`, `Denumire`, `Cantitate`) VALUES ('4', 'Oxigen', '3');


CREATE TABLE if not exists `Spital`.`Stoc URGENT` (
  `ID Produs U` INT NOT NULL AUTO_INCREMENT,
`id produs` int not null,
  `Denumire` VARCHAR(45) NOT NULL,
  `Cantitate` INT NOT NULL,
   `Data livrari` DATETIME On update CURRENT_TIMESTAMP(),
  PRIMARY KEY (`ID Produs U`))
ENGINE = MEMORY;

INSERT INTO `spital`.`stoc urgent`
            (`id produs`,
             `denumire`,
             `cantitate`)
SELECT `id produs`,
       `denumire`,
       `cantitate`
FROM   `spital`.`stoc general` 
where  `cantitate`<'5';

CodePudding user response：

You need to set the value during the insertion, so you must set DEFAULT column's option:

CREATE TABLE if not exists `Spital`.`Stoc URGENT` (
  `ID Produs U` INT NOT NULL AUTO_INCREMENT,
`id produs` int not null,
  `Denumire` VARCHAR(45) NOT NULL,
  `Cantitate` INT NOT NULL,
   `Data livrari` DATETIME DEFAULT (CURRENT_TIMESTAMP   INTERVAL 4 DAY)
                           On update CURRENT_TIMESTAMP(),
  PRIMARY KEY (`ID Produs U`))
ENGINE = MEMORY;

Parenthesis around the expression are compulsory.
You cannot set the same expression in ON UPDATE option.

CodePudding user response：

I suggest that it would be better to create StockUrgent as a view than as a new table. There is not point in storing duplicate values.
I have also taken the liberty of removing the spaces in the table and column name. This avoids the need to surround all the names with backticks and speeds up writing the queries.

CREATE TABLE IF NOT EXISTS StockGeneral (
IdProdus INT ,
  Denumire VARCHAR(45) NOT NULL,
  Cantitate DATE NOT NULL,
  PRIMARY KEY (IdProdus));

INSERT INTO StockGeneral  VALUES ('1', 'Clabax', '2022-03-20');
INSERT INTO StockGeneral  VALUES ('2', 'Betadina', '2022-03-15');
INSERT INTO  StockGeneral  VALUES ('3', 'Paracetamo', '2022-03-24');
INSERT INTO  StockGeneral  VALUES ('4', 'Oxigen', '2022-03-30');

SELECT 
  idProdus,
  Denumire,
  cantitate,
  DateDiff(cantitate,current_date())
FROM StockGeneral

idProdus | Denumire   | cantitate  | DateDiff(cantitate,current_date())
-------: | :--------- | :--------- | ---------------------------------:
       1 | Clabax     | 2022-03-20 |                                  4
       2 | Betadina   | 2022-03-15 |                                 -1
       3 | Paracetamo | 2022-03-24 |                                  8
       4 | Oxigen     | 2022-03-30 |                                 14

CREATE VIEW StockUrgent AS
SELECT 
  idProdus,
  Denumire,
  cantitate
FROM StockGeneral
WHERE DateDiff(cantitate,current_date())< 5;

SELECT * FROM StockUrgent;

idProdus | Denumire | cantitate 
-------: | :------- | :---------
       1 | Clabax   | 2022-03-20
       2 | Betadina | 2022-03-15

db<>fiddle here