table with calculated field

Question

Existing table

CREATE TABLE `dataleitura` (
    `idLeitura` INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
    `DataLeitura` DATE NULL DEFAULT NULL,
    PRIMARY KEY (`idLeitura`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11

---

dice:

"000001"    "2020-01-13"
"000002"    "2020-02-11"
"000003"    "2020-03-13"
"000004"    "2020-04-14"
"000005"    "2020-05-13"
"000006"    "2020-06-12"
"000007"    "2020-07-10"
"000008"    "2020-08-12"
"000009"    "2020-09-11"
"000010"    "2020-10-13"

I need to create a calculated column giving me the days between a date and another "2020-10-13" - "2020-09-11"

CREATE TABLE `dataleitura` (
    `idLeitura` INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
    `DataLeitura` DATE NULL DEFAULT NULL,
    `dias` [GENERATED ALWAYS] AS expressão [VIRTUAL | STORED] constraints
    PRIMARY KEY (`idLeitura`)

as the expression would be, someone can help me

Answer 1

I recommend using a Trigger that when inserting any new field, or updating any data the database automatically calculates or recalculates the data from that column:

--A trigger irá executar toda vez que um novo dado for inserido INSERT;
--É necessário ter a coluna dias já criada no código;
--SYSDATE é data atual que eu utilizei de exemplo;
--Cuidado para não confundir nome de coluna com a da tabela, no caso eu trocaria o nome da coluna.

CREATE TRIGGER trg_atualiza_dia AFTER INSERT
ON dataleitura
FOR EACH ROW
BEGIN
    UPDATE dataleitura SET dias = SYSDATE - DataLeitura
WHERE idLeitura= NEW.idLeitura;
END

It is possible to create another Trigger for AFTER UPDATE, if you will use the command of UPDATE (update) in this table.