Use of MySQL trigger to insert row's week number

Question

I have a table of orders in a MariaDB database that currently has a datetime column. For the dashboard I am building for this database, I have a statistic I would like to display which is "Orders This Week."

My question is this: Can a trigger be used to take the datetime from a just-inserted order and insert the week number of that date into another column? If so, can I also use SQLAlchemy to match the order's week number to Python's strftime('%U') to see orders in a given week?

Answer 1

You tagged MySQL, so here is what should work for a MySQL trigger on your Before Insert trigger.

 DELIMITER $$
 CREATE DEFINER=`USERNAMEHERE`@`%` TRIGGER `SCHEMANAMEHERE`.`TABLENAMEHERE_BEFORE_INSERT` AFTER INSERT 
 ON `TABLENAMEHERE` 
 FOR EACH ROW
 BEGIN
 SET NEW.WeekNumber = WEEKOFYEAR(NEW.InsertedDateTime);
 END$$
 DELIMITER ;