The German clock time of the US open, week by week

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

SELECT
    toString(week)                                                            AS week,
    any(formatDateTime(toTimeZone(us_open_et, 'Europe/Berlin'), '%H:%M'))     AS german_open,
    any(formatDateTime(toTimeZone(us_close_et, 'Europe/Berlin'), '%H:%M'))    AS german_close,
    any(toHour(toTimeZone(us_open_et, 'Europe/Berlin')) - toHour(us_open_et)) AS berlin_minus_et_hours
FROM
(
    SELECT
        toMonday(date)                                                      AS week,
        toDateTime(concat(toString(date), ' 09:30:00'), 'America/New_York') AS us_open_et,
        toDateTime(concat(toString(date), ' 16:00:00'), 'America/New_York') AS us_close_et
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= today() - 400
)
GROUP BY week
ORDER BY week