2,173 answered market questions

WITH ny_open 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('2026-03-02') AND toDate('2026-04-10')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) = 570
    GROUP BY session_date
)
SELECT session_date AS date,
       formatDateTime(session_date, '%b %e') AS date_label,
       formatDateTime(toTimeZone(open_utc, 'Europe/London'), '%H:%i') AS london_local_open,
       formatDateTime(toTimeZone(open_utc, 'Europe/Berlin'), '%H:%i') AS frankfurt_local_open,
       round(((toHour(toTimeZone(open_utc, 'Europe/London')) * 60
               + toMinute(toTimeZone(open_utc, 'Europe/London')) + 1440 - 570) % 1440) / 60, 1) AS london_hours_ahead,
       round(((toHour(toTimeZone(open_utc, 'Europe/Berlin')) * 60
               + toMinute(toTimeZone(open_utc, 'Europe/Berlin')) + 1440 - 570) % 1440) / 60, 1) AS frankfurt_hours_ahead
FROM ny_open
ORDER BY date