1

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 / row 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?

cssdev
  • 59
  • 7

2 Answers2

1

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;
Musical Coder
  • 449
  • 8
  • 14
  • Yes, this would be one of the correct approaches, the one I'd prefer but also there's another fun way called Calculated Columns, you might check that too. here's the link https://stackoverflow.com/questions/11058387/how-do-you-add-a-computed-column-to-a-table – Aurora Oct 20 '21 at 19:39
  • This seems to use time_when_gotten_up and time_when_gone_to_bed from the same row, while I need to use the bedtime from the day / row before. Or am I misunderstanding what's happening? – cssdev Oct 20 '21 at 19:51
  • Thanks @Musical Coder, I was able to create a solution starting from your answer. – cssdev Oct 20 '21 at 22:17
0

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;
cssdev
  • 59
  • 7