如何在MySQL中将Timestamp转换为波斯（太阳历/贾拉利历）日期字符串

MySQL本身没有原生支持波斯太阳历（贾拉利历/Hijri Shamsi）的转换函数，你需要通过自定义SQL函数来实现这个需求。下面是完整的实现方案：

1. 自定义公历转波斯历的核心函数 #

先创建一个基础函数，负责将公历的年、月、日转换为波斯历格式：

DELIMITER //
CREATE FUNCTION GregorianToJalali(g_year INT, g_month INT, g_day INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE jy, jm, jd INT;
    DECLARE g_leap, j_leap, march INT;
    DECLARE num, i INT;

    -- 处理公历1-2月的偏移（对应上一年的13-14月）
    IF g_month < 3 THEN
        SET g_year = g_year - 1;
        SET g_month = g_month + 12;
    END IF;

    -- 判断公历闰年
    SET g_leap = 0;
    IF (g_year % 4 = 0 AND g_year % 100 != 0) OR (g_year % 400 = 0) THEN
        SET g_leap = 1;
    END IF;
    SET march = 28 + g_leap;

    -- 计算从公历1600年到目标日期的总天数
    SET num = FLOOR((g_year - 1600) / 400) * 102;
    SET num = num + FLOOR((g_year - 1600) % 400 / 100) * 25;
    SET num = num + FLOOR(((g_year - 1600) % 100) / 4);
    SET num = num + march + g_day - 1;

    -- 累加目标月份之前的所有天数
    FOR i IN 1..g_month-1 DO
        IF i IN (1,3,5,7,8,10,12) THEN
            SET num = num + 31;
        ELSEIF i IN (4,6,9,11) THEN
            SET num = num + 30;
        ELSE
            SET num = num + march;
        END IF;
    END FOR;

    -- 转换为波斯历年
    SET jy = 979 + FLOOR(num / 366);
    SET num = num % 366;

    -- 判断波斯历闰年
    SET j_leap = 0;
    IF num = 0 THEN
        SET j_leap = 1;
        SET num = 366;
    END IF;

    -- 计算波斯历月和日
    IF num <= 186 THEN
        SET jm = FLOOR((num - 1) / 31) + 1;
        SET jd = (num - 1) % 31 + 1;
    ELSE
        SET num = num - 186;
        SET jm = FLOOR((num - 1) / (29 + j_leap)) + 7;
        SET jd = (num - 1) % (29 + j_leap) + 1;
    END IF;

    RETURN CONCAT(jy, '/', LPAD(jm, 2, '0'), '/', LPAD(jd, 2, '0'));
END //
DELIMITER ;

2. 时间戳转波斯历的封装函数 #

再创建一个更易用的封装函数，直接接收UNIX时间戳返回波斯历日期：

DELIMITER //
CREATE FUNCTION UnixTimestampToJalali(unix_ts BIGINT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE g_year, g_month, g_day INT;
    -- 将时间戳转换为公历的年、月、日
    SET g_year = YEAR(FROM_UNIXTIME(unix_ts));
    SET g_month = MONTH(FROM_UNIXTIME(unix_ts));
    SET g_day = DAY(FROM_UNIXTIME(unix_ts));
    -- 调用核心转换函数
    RETURN GregorianToJalali(g_year, g_month, g_day);
END //
DELIMITER ;

3. 使用示例 #

直接调用封装函数即可完成转换：

-- 转换当前时间戳为波斯历
SELECT UnixTimestampToJalali(UNIX_TIMESTAMP()) AS jalali_date;

-- 转换指定时间戳（比如2024-05-20 00:00:00对应的时间戳）
SELECT UnixTimestampToJalali(1716144000) AS jalali_date;

注意事项 #

• 函数已处理公历和波斯历的闰年、月份天数差异，转换结果准确
• 若使用旧版本MySQL（不支持LPAD），可替换为IF(jm <10, CONCAT('0',jm), jm)实现补零
• 创建函数需要CREATE ROUTINE权限，执行前确保权限充足

内容的提问来源于stack exchange，提问作者s.abbaasi