MySQL convert timestamp to Persian (Hijri shamsi or jalali) date string

Question

I want to convert a timestamp to Persian (Hijri shamsi or jalali) calendar string in MySQL. Similar approaches are presented in SQLServer but I want to do it in MySQL.

Answer 1

Here is a function to convert a mysql timestamp to a Persian or Shamsi (Jalali) string date:

    FUNCTION `toShamsiDate`(g_date DATE) RETURNS varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
    DETERMINISTIC
BEGIN
    DECLARE shamsi_year INT;
    DECLARE shamsi_month INT;
    DECLARE shamsi_day INT;
    DECLARE shamsi_date VARCHAR(10);
    DECLARE total_days INT;
    DECLARE temp_days INT;
    DECLARE leap INT;

    -- Calculate the number of days between the first day of the Shamsi calendar in Gregorian calendar and the input date
    SET total_days = DATEDIFF(g_date, '0622-03-21');

    -- Determine the Shamsi year
    SET shamsi_year = FLOOR(total_days / 365.24219858156) + 1;
    
    -- Determine the leap year
    SET leap = (greatest(0, mod(shamsi_year - floor(shamsi_year / 33) * 33, 33)) IN (1, 5, 9, 13, 17, 22, 26, 30));

    -- Calculate the number of days in the current Shamsi year up until the input date
    SET temp_days = total_days - FLOOR((shamsi_year - 1) * 365.24219858156);

    -- Determine the Shamsi month and day
    IF temp_days < 1*31 THEN
        SET shamsi_month = 1;
        SET shamsi_day = temp_days;
    ELSEIF temp_days < 2*31 THEN
        SET shamsi_month = 2;
        SET shamsi_day = temp_days - (1*31);
    ELSEIF temp_days < 3*31 THEN
        SET shamsi_month = 3;
        SET shamsi_day = temp_days - (2*31);
    ELSEIF temp_days < 4*31 THEN
        SET shamsi_month = 4;
        SET shamsi_day = temp_days - (3*31);
    ELSEIF temp_days < 5*31 THEN
        SET shamsi_month = 5;
        SET shamsi_day = temp_days - (4*31);
    ELSEIF temp_days < 6*31 THEN
        SET shamsi_month = 6;
        SET shamsi_day = temp_days - (5*31);
    ELSEIF temp_days < (6*31)+(1*30) THEN
        SET shamsi_month = 7;
        SET shamsi_day = temp_days - (6*31);
    ELSEIF temp_days < (6*31)+(2*30) THEN
        SET shamsi_month = 8;
        SET shamsi_day = temp_days - ((6*31)+(1*30));
    ELSEIF temp_days < (6*31)+(3*30) THEN
        SET shamsi_month = 9;
        SET shamsi_day = temp_days - ((6*31)+(2*30));
    ELSEIF temp_days < (6*31)+(4*30) THEN
        SET shamsi_month = 10;
        SET shamsi_day = temp_days - ((6*31)+(3*30));
    ELSEIF temp_days < (6*31)+(5*30) THEN
        SET shamsi_month = 11;
        SET shamsi_day = temp_days - ((6*31)+(4*30));
    ELSEIF temp_days <= (6*31)+(5*30)+(29+leap) THEN
        SET shamsi_month = 12;
        SET shamsi_day = temp_days - ((6*31)+(4*30)+(29+leap));
    END IF;

    -- Format the Shamsi date as a string in the 'YYYY-MM-DD' format
    SET shamsi_date = CONCAT(shamsi_year, '-', LPAD(shamsi_month, 2, '0'), '-', LPAD(shamsi_day, 2, '0'));

    -- Return the Shamsi date
    RETURN shamsi_date;
END