0

i have field diff_clock and diff_schedule

i have set the diff_clock with these sql ( trigger )

CONCAT(
            FLOOR(TIMESTAMPDIFF(SECOND, NEW.clock_in_time, NEW.clock_out_time) / 3600 / 24), ' Days ',
            FLOOR(MOD(TIMESTAMPDIFF(SECOND, NEW.clock_in_time, NEW.clock_out_time), 3600 * 24 ) / 3600), ' Hours ',
            FLOOR(MOD(TIMESTAMPDIFF(SECOND, NEW.clock_in_time, NEW.clock_out_time), 3600) / 60), ' Minutes ',
            MOD(TIMESTAMPDIFF(SECOND, NEW.clock_in_time, NEW.clock_out_time), 60), ' Seconds '
        );

and for diff_schedule i only store days, hours, and minutes

so here the values ( Varchar ) :

diff_clock: 1 Days 11 Hours 45 Minutes 22 Seconds

diff_schedule: 1 Days 2 Hours 43 minutes

id diff_clock diff_schedule is_on_time
1 1 Days 11 Hours 45 Minutes 22 Seconds 1 Days 2 Hours 43 minutes how ?

then how to compare if the diff_clock > diff_schedule would print true ?

full code the trigger

CREATE TRIGGER form_bu BEFORE UPDATE
ON form FOR EACH ROW
BEGIN
    IF NEW.clock_out_time IS NOT NULL  THEN
        SET NEW.diff_clock = CONCAT(
            FLOOR(TIMESTAMPDIFF(SECOND, NEW.clock_in_time, NEW.clock_out_time) / 3600 / 24), ' Days ',
            FLOOR(MOD(TIMESTAMPDIFF(SECOND, NEW.clock_in_time, NEW.clock_out_time), 3600 * 24 ) / 3600), ' Hours ',
            FLOOR(MOD(TIMESTAMPDIFF(SECOND, NEW.clock_in_time, NEW.clock_out_time), 3600) / 60), ' Minutes ',
            MOD(TIMESTAMPDIFF(SECOND, NEW.clock_in_time, NEW.clock_out_time), 60), ' Seconds '
        );
    END IF;
END $$
DELIMITER ;

its good choice to replace all string with empty string then convert or parse to integer then compare or anyone have conclusion ? should i store to other field that save the timeepoch ?

https://stackoverflow.com/a/10839219/8122500

SELECT UNIX_TIMESTAMP(yourfield) FROM yourtable;
Yogi Arif Widodo
  • 563
  • 6
  • 22
  • 2
    Since those strings represent a duration, you need to convert them to formats that can be easily compared, either as strings or numbers. So if you convert them to a normalized format, e.g. "1 Days 11 Hours 45 Minutes 22 Seconds" would become "001 11:45:22", you could then use string comparison to determine which is greater. Another way would be to convert to an absolute number of seconds, and compare them as numbers. Either way, you have to do some string manipulation and/or some math to derive the resulting values to be compared. – kmoser Jul 29 '22 at 04:26
  • Do backward. Store the values as second amount. Convert them to verbal representation with generated columns. – Akina Jul 29 '22 at 04:34

0 Answers0