Google BigQuery公历转伊斯兰历及筛选指定伊斯兰历月份方法问询

BigQuery并不支持SQL Server那样的CONVERT(datetime, value, 131)语法，不过可以借助内置的ICU日历支持来完成公历到伊斯兰历的转换，同时实现回历9月的筛选需求。以下是具体方案：

核心思路 #

1. 先将字符串格式的公历日期转换为BigQuery标准DATE类型
2. 使用FORMAT_DATE结合伊斯兰历（推荐用乌姆古拉历Islamic-UmmAlQura，这是主流官方回历）生成指定格式的回历日期字符串
3. 通过EXTRACT函数结合日历参数提取回历月份，用于筛选第9月

完整示例SQL #

假设你的表名为your_table，date列是格式为DD-MM-YYYY的字符串（如果是MM-DD-YYYY，请修改PARSE_DATE的格式参数）：

WITH processed_data AS (
  SELECT
    date AS original_gregorian_str,
    -- 转换为BigQuery标准DATE类型
    PARSE_DATE('%d-%m-%Y', date) AS gregorian_date,
    -- 生成DD-MM-YYYY格式的伊斯兰历日期
    FORMAT_DATE('%d-%m-%Y', PARSE_DATE('%d-%m-%Y', date), 'Islamic-UmmAlQura') AS hijri_date,
    -- 提取伊斯兰历月份（数字形式）
    EXTRACT(MONTH FROM PARSE_DATE('%d-%m-%Y', date) USING CALENDAR 'Islamic-UmmAlQura') AS hijri_month
  FROM your_table
)
-- 筛选伊斯兰历第9月的记录
SELECT *
FROM processed_data
WHERE hijri_month = 9

关键说明 #

• Islamic-UmmAlQura是沙特阿拉伯官方使用的回历版本，比通用Islamic日历的准确性更高，推荐使用
• 如果你的原始日期格式不是DD-MM-YYYY，请调整PARSE_DATE中的格式符：
  • 月-日-年：%m-%d-%Y
  • 年-月-日：%Y-%m-%d
• 回历第9月为莱麦丹月，对应数字9，直接通过hijri_month = 9即可筛选

内容的提问来源于stack exchange，提问作者m996