Use functions time in MySQL Stored Function

Question

I'm trying to create an execution log of a function stored within MySQL.

Have it like this:

Log table:

CREATE TABLE log_execution (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   task VARCHAR(255) NOT NULL,
   start_time TIMESTAMP(6) NOT NULL,
   end_time TIMESTAMP(6));

Function:

CREATE DEFINER=`root`@`localhost` FUNCTION `log_execution`(p_task VARCHAR(255))
RETURNS INT(11)
BEGIN
   DECLARE v_start_time TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6);
   INSERT INTO log_execution(task, start_time) VALUES(p_task, v_start_time);

   SET @SLP = (SELECT SLEEP(5));

   UPDATE log_execution SET end_time = CURRENT_TIMESTAMP(6) WHERE start_time = v_start_time;
   RETURN ROW_COUNT();
END;

But when I run it, the start time and end time are always the same, even if it takes seconds to run.

Does anyone know if it's a bug and how to solve it?

I'm using MySQL 5.7.40.

That when recording the log, correctly record the beginning and end of the execution.

Answer 1

CURRENT_TIMESTAMP() is a synonym for the NOW() function.

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_now says:

NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.