CODEWITHSUNDEEP
mysqlcalculated-columnscalculated-field
Palendrone
Palendrone

Reputation: 363

Calculated Column in MySQL from Array / Formula

I'm very new to MySQL database. In Excel I have a formula that looks at the date cell and then states from that what Season the row of data is from.

In Excel I use this formula which looks at the month and day to determine which if the values from the array it falls into:

=LOOKUP(TEXT([DATE_CELL],"mmdd"),{"0101","0321","0621","0922","1221";"Winter","Spring","Summer","Autumn","Winter"})

Is there any way to setup a view or tag this onto a table in MySQL in order to update the season by itself? Many thanks for taking a look.

Upvotes: 0

Views: 139

Answers (1)

Cedric Simon
Cedric Simon

Reputation: 4659

I would create a function like this:

CREATE DEFINER = 'root'@'localhost' FUNCTION `getSeason`(P_date DATE)
    RETURNS varchar(10) CHARSET latin1
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE v_season VARCHAR(10);
DECLARE v_month integer;
DECLARE v_day integer;
select date_format(P_date,'%m'),date_format(P_date,'%d') into v_month,v_day;
if (v_month<3 or (v_month=3 and v_day<21)) then 
     set  v_season="Winter";
else if (v_month<6 or (v_month=6 and v_day>=21)) then 
     set  v_season="Spring";
else if (v_month<9 or (v_month=9 and v_day>=21)) then 
    set   v_season="Summer";
else if (v_month<12 or (v_month=12 and v_day<=21)) then 
    set   v_season="Autumn";
else
 set  v_season="Winter";
end if;
end if;
end if;
end if;
  RETURN v_season;
END;

And use the function in your WHERE clause: where getSeason(myDaeField)='Winter' Or in your select :select getSeason(myDateField) as Season

Upvotes: 1

Related Questions