Calculate values after insert directly in mysql-CodePudding

I have a mysql database with four columns: date, time when gotten up, time when gone to bed and hours slept. Everyday, the date and both times are entered via an html form using php. My goal is to automatically calculate the hours slept and insert them using only the database. I have implemented something similar like this with DOUBLE AS in the past but the problem in my current case is that I need the bedtime from the day before to do this.

So my question is this: In mysql (or another rdbms), is there a way to calculate the difference of two values which belong to different columns and different rows of a table whenever a new row is inserted?

CodePudding user response：

I believe what you are looking for is something along the lines of...

CREATE TRIGGER trigger_name
    BEFORE UPDATE ON table_name
    FOR EACH ROW
UPDATE table_name
    SET hours_slept = HOUR(TIMEDIFF(time_when_gotten_up, time_when_gone_to_bed))
    WHERE hours_slept IS NULL;

CodePudding user response：

Using @Musical Coder's answer as a template, I was able to create a working solution with subqueries and DATE_SUB():

CREATE TRIGGER trigger_name
    BEFORE INSERT ON table_name
    FOR EACH ROW
SET NEW.hours_slept =
    HOUR(TIMEDIFF(NEW.sleep_end, (SELECT sleep_start FROM table_name WHERE day = DATE_SUB(NEW.day, INTERVAL 1 DAY))))
      MINUTE(TIMEDIFF(NEW.sleep_end, (SELECT sleep_start FROM table_name WHERE day = DATE_SUB(NEW.day, INTERVAL 1 DAY)))) / 60;