mysqlcalendartimestamppersian-calendarjalali-calendar

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


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.


Solution

  • 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