London and Tokyo distance from New York, session by session, October to November 2025

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('2025-10-06') AND toDate('2025-11-07')
      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, 'Asia/Tokyo'), '%H:%i') AS tokyo_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, 'Asia/Tokyo')) * 60
               + toMinute(toTimeZone(open_utc, 'Asia/Tokyo')) + 1440 - 570) % 1440) / 60, 1) AS tokyo_hours_ahead
FROM ny_open
ORDER BY date