How to write SQL command that automatically assigns values from an equation?

• I have this table called Ingredients, with these attributes:

create table Ingredients (ing_id int not null, 
ing_name varchar(40), preserve_time int, ing_bought date, freshness int, 
primary key (ing_id));

• Let’s say I give it these values:

insert into Ingredients values (1, "Meat", 18, "2020-10-20", null);

I’m trying to create a SQL command that automatically assigns the ingredients’ freshness depending on the ingredient’s preserve time, date bought & today’s date.

The equation I want to use is:

freshness = 100 - ((today - ing_bought) / preserve_time) * 100**

I was able to do it this way, but it’s not what I want, since I don’t know how to get today’s date automatically nor to convert only the day from ing_bought which it’s data type is date:

update Ingredients set freshness = 100 - (((30-20) / preserve_time) * 100 
where ing_id = 1;

So I was wondering since I’m new to the database concept & MYSQL, is there a command which gives me the current day as an int & how to get only the day from “date” data type & convert it to int so I can calculate it?

Answer

Suppose you can use this Update statement:

• Now() function, give the current date.
• DATEDIFF() function, give the difference of two dates in number of days.

Code:

update Ingredients set freshness = 100 - (DATEDIFF(now(), ing_bought) / preserve_time)  * 100 where ing_id = 1;

Then you can view result like this:

Select  *, DATE(NOW()) as today, DATEDIFF(now(), ing_bought) as days from Ingredients

[RESULT]:

[EDIT:] live example.

create table Ingredients (ing_id int not null, 
 ing_name varchar(40), preserve_time int, ing_bought date, freshness int, 
 primary key (ing_id));
 
 insert into Ingredients values (1, "Meat", 18, "2020-10-20", null);
 
 

 ✓

 ✓
 

select * from Ingredients

 ing_id | ing_name | preserve_time | ing_bought | freshness
 -----: | :------- | ------------: | :--------- | --------:
       1 | Meat     |            18 | 2020-10-20 |      null
 

update Ingredients set freshness = 100 - (DATEDIFF(now(), ing_bought) / preserve_time)  * 100 
 where ing_id = 1;

 ✓

select  *, DATE(NOW()) as today, DATEDIFF(now(), ing_bought) as days from Ingredients

 ing_id | ing_name | preserve_time | ing_bought | freshness | today      | days
 -----: | :------- | ------------: | :--------- | --------: | :--------- | ---:
      1 | Meat     |            18 | 2020-10-20 |         6 | 2020-11-06 |   17

db<>fiddle here