The New York open on two clocks: month-end sessions, August 2024 to July 2026

WITH ny_opens AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           min(window_start) AS open_utc
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) BETWEEN toDate('2024-08-01') AND toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) = 570
    GROUP BY session_date
)
SELECT formatDateTime(toStartOfMonth(session_date), '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(toStartOfMonth(session_date), 'MMMM yyyy') AS month_label,
       formatDateTime(toTimeZone(argMax(open_utc, session_date), 'America/New_York'), '%H:%i') AS ny_open_et_clock,
       formatDateTime(argMax(open_utc, session_date), '%H:%i') AS ny_open_utc_clock,
       toHour(toTimeZone(argMax(open_utc, session_date), 'America/New_York')) * 60
           + toMinute(toTimeZone(argMax(open_utc, session_date), 'America/New_York')) AS ny_open_et_minutes,
       toHour(argMax(open_utc, session_date)) * 60
           + toMinute(argMax(open_utc, session_date)) AS ny_open_utc_minutes
FROM ny_opens
GROUP BY toStartOfMonth(session_date)
ORDER BY toStartOfMonth(session_date)