Mysql – Use of MySQL trigger to insert row’s week number

mariadbMySQLpythonsqlalchemytrigger

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?

Best Answer

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 ;

Related Solutions

Mysql – How to improve the SQL statement with weekly results with week starting on Thursday or any other day of the week

Here is a query I wrote up to give you the most recent thursday and the ending wednesday

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

Here is an example for today, 2011-09-21

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2011-09-15 00:00:00 | 2011-09-21 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Just replace the NOW() function calls with whatever datetime you like and you will have the week starting Thursday all the time for the give datetime you choose.

Here is another example using the specific date '2011-01-01'

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE('2011-01-01') - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE('2011-01-01') dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2010-12-30 00:00:00 | 2011-01-05 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Your query of table referencing today would resemble something like this:

SELECT * from `table`,
(SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A) M
WHERE `date` >= thu_beg
AND `date` <= wed_end;

Give it a Try !!!

UPDATE 2011-09-22 16:27 EDT

This was my proposed query for marking Thu-Wed.

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

How about other weeks ???

(SELECT SUBSTR('6012345',wkndx,1) does the week starting Mon ending Sun
(SELECT SUBSTR('5601234',wkndx,1) does the week starting Tue ending Mon
(SELECT SUBSTR('4560123',wkndx,1) does the week starting Wed ending Tue
(SELECT SUBSTR('3456012',wkndx,1) does the week starting Thu ending Wed
(SELECT SUBSTR('2345601',wkndx,1) does the week starting Fri ending Thu
(SELECT SUBSTR('1234560',wkndx,1) does the week starting Sat ending Fri
(SELECT SUBSTR('0123456',wkndx,1) does the week starting Sun ending Sat

Mysql – BEFORE INSERT trigger in MySQL

I have dealt with this question before. MySQL has a very flimsy architecture when it comes to triggers.

I have written posts before on how to break triggers midstream. The solution is rather unsavory and would leave a pure SQL developer with a bad taste in the month.

With that said, here are my posts that I hope would help:

Apr 25, 2011 : Trigger in MySQL to prevent insertion
Dec 23, 2011 : check constraint does not work?

Since you are a newbie, writing trigger code requires changing the delimiter to something other than a semicolon and then changing back.

DELIMITER $$

CREATE TRIGGER chk_dates  
BEFORE INSERT ON verlof_aanvragen  
FOR EACH ROW  
BEGIN  
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY) 
THEN  
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message';
END IF;
END $$

DELIMITER ;

Please look back at my posts to see how I do this in my code.

UPDATE 2013-06-12 11:34 EDT

Here is your last comment

I am using above trigger with mysql server v 5.5.24 and it works fine. This is my local test site. Now I want to use it at my webhost, they have mysql server v 5.1.56 ... and it won; t work ! Am I missing something?

The problem is the MySQL version. If you look back in my two trigger posts, I used black market techniques to break the trigger midstream. This is necessary for older versions of MySQL. Notice what I stated on Apr 25, 2011 in Trigger in MySQL to prevent insertion :

The reason the book suggests preempting the Trigger in this manner stems from the fact that the MySQL Stored Procedure Language did not have SIGNAL implemented into the language (of course, SIGNAL is ANSI standard).

The authors of the book created work arounds by calling on SQL statements that are syntactically correct but fail at runtime. Pages 144-145 (Chapter 6: Error Handling) of the book gives these examples on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL emulation (Examples 6-19 and 6-20).

I directly quoted Pages 254-256 of the Book

dlckxmd

Please note the following:

MySQL 5.5.8 was released as GA Dec 03, 2010
The book was written March 2006

Therefore, signal processing DOES NOT WORK IN MySQL 5.1.

You will have to do the following:

Rewrite the trigger in MySQL 5.1 to break midstream
The trigger you wrote in the MySQL 5.5 database should stay the way it is now

That's why the trigger does not work in MySQL 5.1.

UPDATE 2013-06-12 11:44 EDT

Here is what you can do to the trigger in the MySQL 5.1.56 database

DELIMITER $$

CREATE TRIGGER chk_dates  
BEFORE INSERT ON verlof_aanvragen  
FOR EACH ROW  
BEGIN  
  DECLARE dummy INT;
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY) 
THEN  
  SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';
END IF;
END $$

DELIMITER ;

Since mysql.user has no column called used, the query does not execute, breaking the trigger.