米国株の取引時間を日本時間で解説：夏は22:30、冬は23:30開始

SELECT
    month,
    concat(toString(toYear(toDate(month))), '年', toString(toMonth(toDate(month))), '月') AS month_label,
    sum(sessions_at_minute)                                        AS sessions,
    argMax(busiest_jst, (sessions_at_minute, busiest_jst))         AS typical_busiest_minute_jst,
    argMax(busiest_jst_hour, (sessions_at_minute, busiest_jst))    AS typical_busiest_hour_jst,
    max(sessions_at_minute)                                        AS sessions_at_that_minute
FROM
(
    SELECT
        toString(toStartOfMonth(session_day)) AS month,
        busiest_jst,
        busiest_jst_hour,
        count()                               AS sessions_at_minute
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York'))     AS session_day,
            toTimeZone(argMax(window_start, volume), 'Asia/Tokyo')   AS busiest_ts,
            formatDateTime(busiest_ts, '%H:%i')                      AS busiest_jst,
            round(toHour(busiest_ts) + toMinute(busiest_ts) / 60, 2) AS busiest_jst_hour
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime('2025-10-01 00:00:00', 'America/New_York')
          AND window_start <  toDateTime('2026-09-01 00:00:00', 'America/New_York')
        GROUP BY session_day
    )
    GROUP BY month, busiest_jst, busiest_jst_hour
)
GROUP BY month
ORDER BY month

SELECT
    jst_time,
    et_time,
    volume_share_pct
FROM
(
    SELECT
        formatDateTime(toStartOfInterval(toTimeZone(window_start, 'Asia/Tokyo'), INTERVAL 30 MINUTE), '%H:%i')       AS jst_time,
        formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
        toHour(toTimeZone(window_start, 'America/New_York'))                                                           AS et_hour,
        round(100 * sum(toFloat64(volume)) / sum(sum(toFloat64(volume))) OVER (), 2)                                  AS volume_share_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-06-01 00:00:00', 'America/New_York')
      AND window_start <  toDateTime('2026-07-01 00:00:00', 'America/New_York')
    GROUP BY jst_time, et_time, et_hour
)
WHERE et_hour BETWEEN 9 AND 16
ORDER BY et_time

SELECT
    jst_time,
    et_time,
    volume_share_pct
FROM
(
    SELECT
        formatDateTime(toStartOfInterval(toTimeZone(window_start, 'Asia/Tokyo'), INTERVAL 30 MINUTE), '%H:%i')       AS jst_time,
        formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
        toHour(toTimeZone(window_start, 'America/New_York'))                                                           AS et_hour,
        round(100 * sum(toFloat64(volume)) / sum(sum(toFloat64(volume))) OVER (), 2)                                  AS volume_share_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-01-01 00:00:00', 'America/New_York')
      AND window_start <  toDateTime('2026-02-01 00:00:00', 'America/New_York')
    GROUP BY jst_time, et_time, et_hour
)
WHERE et_hour BETWEEN 9 AND 16
ORDER BY et_time

SELECT
    toString(date)                                                                            AS holiday_date,
    arrayElement(['月', '火', '水', '木', '金', '土', '日'], toDayOfWeek(date))                    AS weekday_jp,
    any(name)                                                                                 AS holiday,
    any(status)                                                                               AS day_status,
    if(timeZoneOffset(toDateTime(date, 'America/New_York')) = -14400, '夏時間', '冬時間')          AS us_clock,
    concat(toString(toMonth(date)), '/', toString(toDayOfMonth(date)), '夜から',
           toString(toMonth(addDays(date, 1))), '/', toString(toDayOfMonth(addDays(date, 1))), '朝') AS jst_night_removed,
    any(if(status = 'early-close',
           formatDateTime(toTimeZone(close, 'Asia/Tokyo'), '%m/%d %H:%i'),
           ''))                                                                               AS jst_early_close,
    dateDiff('day', today(), date)                                                            AS days_away
FROM global_markets.stocks_market_holidays
WHERE date >= today()
GROUP BY date
ORDER BY date