MySQL Calculate Derived Attribute

Question

I have a derived attribute Fine in a MySQL table, whose value is

(CurrentDate - DueDate) * 0.5

CurrentDate and DueDate are stored in the table in the Date format.

How can I specify this formula for Fine attribute in my table

CREATE TABLE IF NOT EXISTS Loan (
         loanID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
         BorrowDate  DATE       DEFAULT NULL,
         ReturnDate         DATE       DEFAULT NULL,
         DueDate         DATE       DEFAULT NULL,
--- Fine... What do I write for Fine here-------
         userID    INT UNSIGNED  NOT NULL,           
         itemID    INT UNSIGNED  NOT NULL,      
         PRIMARY KEY  (loanID) ,
         FOREIGN KEY (userID) REFERENCES LibraryUser (userID),
         FOREIGN KEY (itemID) REFERENCES Items (itemID)
       );

SQL is the language, which varies with different database products. Which RDBMS are you using? SQL Server/MySQL/Oracle etc. — Bridge, Jan 25 '13 at 16:34
@HamletHakobyan- I want fine in days... Fine is number of days multiplied by 0.5 — user1628340, Jan 25 '13 at 16:35
This question is lacking a lot of information. Please show some code. — Kermit, Jan 25 '13 at 16:36
@RaphaëlAlthaus- Please include code for whatever you are suggesting — user1628340, Jan 25 '13 at 16:41
Don't make a column at all. Calculate the value in whatever SELECT query you write. — MikeSmithDev, Jan 25 '13 at 16:42
The fine value probably needs to be `GREATEST(0, (CurrentDate-DueDate)*0.5)` to avoid the library owing people money if they return the book before the due date. — Jonathan Leffler, Jan 25 '13 at 17:04
@JonathanLeffler- Please provide the proper MySQL syntax for the same. — user1628340, Jan 25 '13 at 17:35
@user1628340: Errm...what was wrong with the syntax of `GREATEST(0, (CurrentData-DueDate)*0.5)` — the [Fine Manual](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_greatest) says that should work in MySQL 5.0 or later. — Jonathan Leffler, Jan 25 '13 at 21:32

score 5 · Answer 1 · edited May 23 '17 at 12:21

5

You can't have a computed column "as is" in mysql.

Three solutions :

First : use triggers

see for example column calculated from another column

Second : create a view (Fine will exist only in your view, not in your main table).

view can contain computed columns without any problem.

CREATE VIEW v_computedLoan AS
SELECT
loanID, 
BorrowDate, 
ReturnDate, 
CurrentDate,
--etc
(CurrentDate-DueDate)*0.5 as Fine
FROM Loan

Third : keep calculation out of your db. Use this in your queries only.

EDIT : if CurrentDate is really the same as CURRENT_DATE() function (and then should vary), solution with triggers won't work, and CurrentDate shouldn't be stored in any table (but you can still use it in a view).

edited May 23 '17 at 12:21

Community

1
1

answered Jan 25 '13 at 16:41

Raphaël Althaus

59,727
6
96
122

+1 but I don't think a trigger would work... He needs to update that column every day regardless of any table changes. – MikeSmithDev Jan 25 '13 at 16:44
@MikeSmithDev Hmmm, in fact CurrentDate is stored in the table, so it might work. – Raphaël Althaus Jan 25 '13 at 16:52
You should not store the current date in the table. Likewise, any 'age' column is inherently dubious in a stored table. 'Age on Relevant Date' is legitimate (though unnecessary because calculable), but 'current age' is not a good attribute (because, given enough people in the database, it changes for some of the people every day). The question does mention 'current date is stored in the table', but I think that's a typo; it isn't shown and shouldn't be stored. – Jonathan Leffler Jan 25 '13 at 17:07
@JonathanLeffler well, I just saw that in the question, a CurrentDate column exists in the table. But... we don't know what's inside ! (If it's "today", of course, it's a very bad idea). – Raphaël Althaus Jan 25 '13 at 17:10
@JonathanLeffler in fact, you seem to be right (Fine calculation seems to indicate that). Hmmm, or maybe there's a confusion between returnDate (which is present in question's table sample) and CurrentDate (which is not) – Raphaël Althaus Jan 25 '13 at 17:12

MySQL Calculate Derived Attribute

1 Answers1