dst_shift_dates

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.

WITH
opens AS
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        toHour(min(window_start))                            AS open_hour_utc
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2022-01-03 00:00:00')
      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_date
),
shifts AS
(
    SELECT
        session_date,
        open_hour_utc,
        lagInFrame(open_hour_utc) OVER (ORDER BY session_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_open_hour_utc
    FROM opens
)
SELECT
    toString(session_date)                          AS shift_date,
    formatDateTime(session_date, '%b %e, %Y')       AS shift_date_label,
    concat(toString(prev_open_hour_utc + 8), ':30') AS sgt_open_before,
    concat(toString(open_hour_utc + 8), ':30')      AS sgt_open_after,
    toUInt8(open_hour_utc + 8)                      AS sgt_open_hour
FROM shifts
WHERE prev_open_hour_utc > 0
  AND open_hour_utc != prev_open_hour_utc
ORDER BY session_date