Sydney open and close of the first US session each 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(toStartOfWeek(session, 1))                          AS week,
    trimBoth(formatDateTime(toStartOfWeek(session, 1), '%e %b %Y')) AS week_label,
    argMin(sydney_open, session)                                 AS sydney_open,
    argMin(sydney_close, session)                                AS sydney_close,
    argMin(hours_ahead_of_new_york, session)                     AS hours_ahead_of_new_york
FROM
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York'))                                        AS session,
        formatDateTime(toTimeZone(min(window_start), 'Australia/Sydney'), '%H:%i')                   AS sydney_open,
        formatDateTime(toTimeZone(addMinutes(max(window_start), 1), 'Australia/Sydney'), '%H:%i')    AS sydney_close,
        modulo(toHour(toTimeZone(min(window_start), 'Australia/Sydney'))
               - toHour(toTimeZone(min(window_start), 'America/New_York')) + 24, 24)                 AS hours_ahead_of_new_york
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= today() - 400
      AND window_start <  today() - 4
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
    GROUP BY session
    HAVING toHour(toTimeZone(min(window_start), 'America/New_York')) * 60
           + toMinute(toTimeZone(min(window_start), 'America/New_York')) = 570
)
GROUP BY toStartOfWeek(session, 1)
ORDER BY week