31 lines
887 B
MySQL
31 lines
887 B
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` FUNCTION `util`.`log_formatDate`(vInstance JSON)
|
||
|
|
RETURNS longtext CHARSET utf8mb4 COLLATE utf8mb4_bin
|
||
|
|
DETERMINISTIC
|
||
|
|
BEGIN
|
||
|
|
DECLARE vKeys JSON;
|
||
|
|
DECLARE vPath VARCHAR(255);
|
||
|
|
DECLARE vValue VARCHAR(255);
|
||
|
|
DECLARE i INT DEFAULT 0;
|
||
|
|
DECLARE vCount INT;
|
||
|
|
|
||
|
|
IF vInstance IS NULL THEN
|
||
|
|
RETURN NULL;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
SET vKeys = JSON_KEYS(vInstance);
|
||
|
|
SET vCount = JSON_LENGTH(vInstance);
|
||
|
|
|
||
|
|
WHILE i < vCount DO
|
||
|
|
SET vPath = CONCAT('$.', JSON_VALUE(vKeys, CONCAT('$[', i ,']')));
|
||
|
|
SET vValue = JSON_VALUE(vInstance, vPath);
|
||
|
|
IF vValue REGEXP '^[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2} [[:digit:]]{2}:[[:digit:]]{2}:[[:digit:]]{2}$' THEN
|
||
|
|
SET vInstance = JSON_SET(vInstance, vPath, DATE_FORMAT(CONVERT_TZ(vValue, '+01:00', '+00:00'), '%Y-%m-%dT%T.%fZ'));
|
||
|
|
END IF;
|
||
|
|
SET i := i + 1;
|
||
|
|
END WHILE;
|
||
|
|
|
||
|
|
RETURN vInstance;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|