ist_open_shift

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,
        toTimeZone(window_start, 'Asia/Kolkata')             AS ist
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= today() - 300
      AND window_start <  today() - 2
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) = 570
)
SELECT
    toString(toMonday(session_date))                            AS week,
    concat(toString(toDayOfMonth(toMonday(session_date))), ' ',
           formatDateTime(toMonday(session_date), '%b %Y'))     AS week_label,
    any(formatDateTime(ist, '%H:%i'))                           AS ist_open_clock,
    min(toHour(ist) * 60 + toMinute(ist))                       AS ist_open_minutes
FROM opens
GROUP BY week, week_label
ORDER BY week