Public
Snippet $251 authored by Julien Muchembled

mysql functions for DB analysis

Edited
functions.sql
Raw
DELIMITER //

CREATE OR REPLACE FUNCTION DateFromTID (tid BIGINT UNSIGNED)
RETURNS DATETIME(6) DETERMINISTIC
BEGIN
  DECLARE months, days, hours, minutes, x INT UNSIGNED;
  DECLARE seconds DECIMAL(8,6);

  SET x = tid >> 32;
  SET seconds = tid % 0x100000000 * 60 / 0x100000000;
  SET minutes = x % 60, x = x DIV 60;
  SET hours = x % 24, x = x DIV 24;
  SET days = 1 + x % 31, x = x DIV 31;
  SET months = 1 + x % 12, x = x DIV 12;

  RETURN CONCAT(
    LPAD(x + 1900, 4, '0'), '-',
    LPAD(months,   2, '0'), '-',
    LPAD(days,     2, '0'), ' ',
    LPAD(hours,    2, '0'), ':',
    LPAD(minutes,  2, '0'), ':',
    IF(seconds < 10, CONCAT('0', seconds), seconds)
  );
END; //

-- limited to ±840h, but usually enough to compare ttid with tid
CREATE OR REPLACE FUNCTION DeltaTID (tid1 BIGINT UNSIGNED, tid2 BIGINT UNSIGNED)
RETURNS TIME(6) DETERMINISTIC
BEGIN
  RETURN TIMEDIFF(DateFromTID(tid1), DateFromTID(tid2));
END; //

CREATE OR REPLACE FUNCTION decompress (x MEDIUMBLOB)
RETURNS MEDIUMBLOB DETERMINISTIC
BEGIN
  RETURN UNCOMPRESS(CONCAT(
    REVERSE(UNHEX(LPAD(HEX(@@max_allowed_packet), 8, '0'))), x));
END; //

-- big records not supported (compression & 0x80)
CREATE OR REPLACE FUNCTION data_length (compression TINYINT, value MEDIUMBLOB)
RETURNS INT UNSIGNED DETERMINISTIC
BEGIN
  RETURN LENGTH(IF(compression & 1, decompress(value), value));
END; //

DELIMITER ;
Styling with Markdown is supported
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!