Working with Date functions mysql and adding day to date while keeping track

Question

So I have this table

CREATE TABLE `chittytransactions` (
  `ChittyTransactionID` int(11) NOT NULL,
  `AuctionID` int(11) NOT NULL,
  `ChittyAccNo` int(11) DEFAULT NULL,
  `Date` datetime DEFAULT NULL,
  `Amount` double DEFAULT NULL,
  `Description` varchar(50) DEFAULT NULL,
  `TransRefence` varchar(50) DEFAULT NULL COMMENT 'Reference from actual Bank transaction',
  `TransStatus` tinyint(1) DEFAULT NULL COMMENT 'If Transaction Pending or Cleared',
  `ClearanceDate` datetime DEFAULT NULL,
  `PaymentMethod` int(1) DEFAULT NULL COMMENT '0- Cash, 1- bank transfer, 2- personal credit etc'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Im creating a trigger that checks the Date and the clearanceDate to see if the clearanceDate is greater than the actual date then add a late fee. I have this so far:

DROP TRIGGER IF EXISTS chitty_before_trig;

DELIMITER ;;
CREATE TRIGGER chitty_before_trig BEFORE INSERT ON chittytransactions
FOR EACH ROW
  BEGIN
  DECLARE `userId` INT(11);

  SELECT `UserId`
  INTO `userId`
  FROM chittyusers
  WHERE ChittyAccNo = NEW.ChittyAccNo;

    IF NEW.ClearanceDate <> NEW.`Date` THEN
            UPDATE `chittyusers` SET LatePaymentFee = 50 WHERE UserId = userId;
    END IF;
 END;;
 DELIMITER;

Using the date functions how can i check if a day has gone by etc and add a calculate late payment for each day?Please anything would be great Thank you.

You actually shouldn't need to store the late fee in the table unless you need to access it a lot, like, *really* a lot. Normally with a value that can always be *derived* from other values in the database (especially if they're in the same table) you would simply calculate them each time you need them. Views are often used for this purpose. — Darwin von Corax, Apr 26 '16 at 18:14
no late fee would depend on the date. like if another day goes by the late fee would change constantly. any idea how? — luffy, Apr 26 '16 at 20:55
so if the cleared date of late fee is greater than the actual date the payment was meant to be made then a late fee payment is added based on the day. maybe like +10 per each day — luffy, Apr 27 '16 at 03:09

score 1 · Accepted Answer · answered Apr 27 '16 at 03:37

If I've understood the question, then something like

IF NEW.ClearanceDate > NEW.`Date` THEN
  UPDATE chittyusers
    SET LatePaymentFee = 10 * DATEDIFF(NEW.ClearanceDate, NEW.`Date`)
    WHERE UserId = userId;
END IF;

would work with your current schema. DATEDIFF(date1, date2) returns the number of days between date1 and date2 - the result is negative if date1 is earlier than date2. Note I've changed the comparison operator from <> to > so the late fee is applied if ClearanceDate is after Date, but not if it's before.

I should point out that having a bare numeric constant like 10 in your code like that is considered very bad form. A slightly better way would be to declare a local variable named something like baseLateFee, set it to 10, and then use the variable in your calculation. A much better alternative, especially if there's any possibility that baseLateFee will ever change, would be to store it in a table somewhere so you can update it without having to find and change all the code that depends on it.

Thanks I'll try this. yea i though about the option of having different table for late payment that changes — luffy, Apr 27 '16 at 04:01

Working with Date functions mysql and adding day to date while keeping track

1 Answers1