美股期权到期时间北京时间对照：2026年第三个周五日历与凌晨截止时刻

SELECT
    toString(expiry)                                                        AS expiration_date,
    concat(toString(toMonth(expiry)), ' 月 ', toString(toDayOfMonth(expiry)), ' 日') AS expiry_label,
    transform(toDayOfWeek(expiry), [4, 5], ['周四', '周五'], '其他')         AS weekday,
    if(expiry = third_friday, '第三个周五', '周五休市，提前至周四')            AS rule,
    if(expiry = third_friday, 0, 1)                                         AS shifted_to_thursday,
    formatDateTime(cutoff_et, '%H:%i', 'America/New_York')                  AS et_cutoff,
    if(timeZoneOffset(cutoff_et) = -14400, 'EDT 夏令时', 'EST 冬令时')        AS us_clock,
    concat(formatDateTime(cutoff_et, '%m-%d', 'Asia/Shanghai'), ' ',
           transform(toDayOfWeek(toTimeZone(cutoff_et, 'Asia/Shanghai')), [5, 6], ['周五', '周六'], '其他'), ' ',
           formatDateTime(cutoff_et, '%H:%i', 'Asia/Shanghai'))             AS beijing_cutoff,
    toHour(toTimeZone(cutoff_et, 'Asia/Shanghai'))                          AS utc8_clock
FROM
(
    SELECT
        cal.third_friday                                                    AS third_friday,
        if(cal.third_friday IN (SELECT date FROM global_markets.stocks_market_holidays WHERE status = 'closed')
           OR (cal.third_friday < today() - 3 AND ifNull(t.bars, 0) = 0),
           cal.third_friday - 1,
           cal.third_friday)                                                AS expiry,
        toDateTime(concat(toString(expiry), ' 16:00:00'), 'America/New_York') AS cutoff_et
    FROM
    (
        SELECT addDays(month_start, ((5 - toDayOfWeek(month_start) + 7) % 7) + 14) AS third_friday
        FROM (SELECT addMonths(toDate('2026-01-01'), arrayJoin(range(12))) AS month_start)
    ) AS cal
    LEFT JOIN
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            count()                                              AS bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDate('2026-01-01')
          AND window_start <  toDate('2027-01-02')
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
        GROUP BY d
    ) AS t ON t.d = cal.third_friday
)
ORDER BY expiry

SELECT
    toString(session_day)                                                   AS session_date,
    concat(toString(toMonth(session_day)), ' 月 ', toString(toDayOfMonth(session_day)), ' 日') AS session_label,
    transform(toDayOfWeek(session_day), [4, 5], ['周四', '周五'], '其他')     AS weekday,
    formatDateTime(last_bar, '%H:%i', 'America/New_York')                   AS last_regular_bar_et,
    formatDateTime(last_bar, '%m-%d %H:%i', 'Asia/Shanghai')                AS last_regular_bar_beijing,
    toHour(toTimeZone(last_bar, 'Asia/Shanghai')) + 1                       AS utc8_close_clock,
    bar_count                                                               AS regular_bar_count
FROM
(
    SELECT
        cal.third_friday                 AS third_friday,
        max(t.d)                         AS session_day,
        argMax(t.last_bar, t.d)          AS last_bar,
        argMax(t.bars, t.d)              AS bar_count
    FROM
    (
        SELECT addDays(month_start, ((5 - toDayOfWeek(month_start) + 7) % 7) + 14) AS third_friday
        FROM (SELECT addMonths(toDate('2026-01-01'), arrayJoin(range(12))) AS month_start)
    ) AS cal
    CROSS JOIN
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            max(window_start)                                    AS last_bar,
            count()                                              AS bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDate('2026-01-01')
          AND window_start <  today() - 3
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
        GROUP BY d
    ) AS t
    WHERE t.d = cal.third_friday OR t.d = cal.third_friday - 1
    GROUP BY cal.third_friday
)
ORDER BY third_friday

SELECT
    formatDateTime(h.hour_start, '%m-%d %H:00', 'Asia/Shanghai')            AS beijing_clock,
    formatDateTime(h.hour_start, '%H:00', 'America/New_York')               AS et_clock,
    multiIf(h.slot < 5, '盘前',
            h.slot = 5, '盘前转常规（21:30 开盘）',
            h.slot < 12, '常规时段',
            '盘后')                                                         AS session_part,
    ifNull(t.volume_millions, 0)                                            AS volume_millions
FROM
(
    SELECT
        arrayJoin(range(16))                                                  AS slot,
        addHours(toDateTime('2026-08-21 04:00:00', 'America/New_York'), slot) AS hour_start,
        toUnixTimestamp(hour_start)                                           AS ts
) AS h
LEFT JOIN
(
    SELECT
        toUnixTimestamp(toStartOfHour(window_start))  AS ts,
        round(toFloat64(sum(volume)) / 1e6, 2)        AS volume_millions
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-08-21 04:00:00', 'America/New_York')
      AND window_start <  toDateTime('2026-08-21 20:00:00', 'America/New_York')
    GROUP BY ts
) AS t ON t.ts = h.ts
ORDER BY h.slot