美股開盤時間台北對照表：夏令冬令與四時段

WITH us_sessions AS
(
    SELECT arrayJoin([
        (1, '夜盤 Overnight',    '20:00:00', '04:00:00', '台北白天，與台股盤中並行'),
        (2, '盤前 Pre-Market',   '04:00:00', '09:30:00', '台北傍晚到深夜'),
        (3, '正常盤 Regular',    '09:30:00', '16:00:00', '台北深夜到隔天清晨'),
        (4, '盤後 After-Hours',  '16:00:00', '20:00:00', '台北隔天清晨到上班前')
    ]) AS s
)
SELECT
    tupleElement(s, 2)                                                       AS session_name,
    concat(substring(tupleElement(s, 3), 1, 5), ' - ',
           substring(tupleElement(s, 4), 1, 5))                              AS et_window,
    concat(
        formatDateTime(toTimeZone(toDateTime(concat('2026-07-15 ', tupleElement(s, 3)), 'America/New_York'), 'Asia/Taipei'), '%H:%i'),
        ' - ',
        formatDateTime(toTimeZone(toDateTime(concat('2026-07-15 ', tupleElement(s, 4)), 'America/New_York'), 'Asia/Taipei'), '%H:%i')
    )                                                                        AS taipei_summer,
    concat(
        formatDateTime(toTimeZone(toDateTime(concat('2026-12-15 ', tupleElement(s, 3)), 'America/New_York'), 'Asia/Taipei'), '%H:%i'),
        ' - ',
        formatDateTime(toTimeZone(toDateTime(concat('2026-12-15 ', tupleElement(s, 4)), 'America/New_York'), 'Asia/Taipei'), '%H:%i')
    )                                                                        AS taipei_winter,
    round(
        modulo(
            dateDiff('minute',
                toDateTime(concat('2026-07-15 ', tupleElement(s, 3)), 'America/New_York'),
                toDateTime(concat('2026-07-15 ', tupleElement(s, 4)), 'America/New_York')
            ) + 1440, 1440) / 60.0, 1)                                       AS session_hours,
    tupleElement(s, 5)                                                       AS taipei_note
FROM us_sessions
ORDER BY tupleElement(s, 1)

SELECT
    toString(toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York')))) AS month,
    formatDateTime(toTimeZone(min(window_start), 'Asia/Taipei'), '%H:%i')          AS taipei_open_first_session,
    formatDateTime(toTimeZone(max(window_start), 'Asia/Taipei'), '%H:%i')          AS taipei_open_last_session,
    toUInt16(toHour(toTimeZone(min(window_start), 'Asia/Taipei')) * 60
           + toMinute(toTimeZone(min(window_start), 'Asia/Taipei')))               AS taipei_open_minute_first,
    toUInt16(toHour(toTimeZone(max(window_start), 'Asia/Taipei')) * 60
           + toMinute(toTimeZone(max(window_start), 'Asia/Taipei')))               AS taipei_open_minute_last
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPY'
  AND window_start >= toDateTime('2025-09-01 00:00:00', 'UTC')
  AND window_start <  toDateTime('2026-10-01 00:00:00', 'UTC')
  AND toHour(toTimeZone(window_start, 'America/New_York')) = 9
  AND toMinute(toTimeZone(window_start, 'America/New_York')) = 30
GROUP BY month
ORDER BY month

SELECT
    concat(leftPad(toString(g.h), 2, '0'), ':00')   AS taipei_hour,
    round(ifNull(v.avg_millions, 0), 2)             AS spy_volume_millions,
    toUInt32(ifNull(v.minutes_traded, 0))           AS minutes_with_bars
FROM
(
    SELECT arrayJoin(range(24)) AS h
) AS g
LEFT JOIN
(
    SELECT
        toHour(toTimeZone(window_start, 'Asia/Taipei'))                      AS h,
        toFloat64(sum(volume))
            / countDistinct(toDate(toTimeZone(window_start, 'America/New_York')))
            / 1e6                                                            AS avg_millions,
        count()                                                              AS minutes_traded
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= today() - 70
      AND window_start <  today() - 2
    GROUP BY h
) AS v ON v.h = g.h
ORDER BY g.h

SELECT
    concat(toString(toMonth(date)), '月', toString(toDayOfMonth(date)), '日') AS date_zh,
    toString(date)                                                           AS holiday_date,
    formatDateTime(date, '%a')                                               AS weekday_et,
    any(name)                                                                AS holiday,
    dateDiff('day', today(), date)                                           AS days_away
FROM global_markets.stocks_market_holidays
WHERE date >= today()
  AND status = 'closed'
GROUP BY date
ORDER BY date

SELECT
    concat(toString(toMonth(date)), '月', toString(toDayOfMonth(date)), '日') AS date_zh,
    formatDateTime(date, '%a')                                               AS weekday_et,
    any(name)                                                                AS holiday,
    any(formatDateTime(toTimeZone(close, 'America/New_York'), '%H:%i'))      AS close_et,
    any(concat(
        toString(toMonth(toTimeZone(close, 'Asia/Taipei'))), '月',
        toString(toDayOfMonth(toTimeZone(close, 'Asia/Taipei'))), '日 ',
        formatDateTime(toTimeZone(close, 'Asia/Taipei'), '%H:%i')
    ))                                                                       AS close_taipei_zh,
    dateDiff('day', today(), date)                                           AS days_away
FROM global_markets.stocks_market_holidays
WHERE date >= today()
  AND status = 'early-close'
GROUP BY date
ORDER BY date