stellar-bigquery-timebounds-above-int64.sql

SELECT
transaction_hash,
time_bounds,
split(regexp_replace(time_bounds, '(^[\\[\\(])|([\\]\\)]$)', ''), ',')[OFFSET(0)] as tbmin,
split(regexp_replace(time_bounds, '(^[\\[\\(])|([\\]\\)]$)', ''), ',')[OFFSET(1)] as tbmax,
FROM `crypto-stellar.crypto_stellar.history_transactions`
WHERE time_bounds IS NOT NULL
AND (
    (
        (char_length(split(regexp_replace(time_bounds, '(^[\\[\\(])|([\\]\\)]$)', ''), ',')[OFFSET(0)]) > 18)
        AND
        (split(regexp_replace(time_bounds, '(^[\\[\\(])|([\\]\\)]$)', ''), ',')[OFFSET(0)] != "9223372036854775807")
    )
    OR
    (
        (char_length(split(regexp_replace(time_bounds, '(^[\\[\\(])|([\\]\\)]$)', ''), ',')[OFFSET(1)]) > 18)
        AND
        (split(regexp_replace(time_bounds, '(^[\\[\\(])|([\\]\\)]$)', ''), ',')[OFFSET(1)] != "9223372036854775807")
    )
)

This query is a little indirect. It finds transactions with time bounds where either the min or max have more than 18 characters and the value is not the value of int64 max. Time bounds are stored as strings and big query does not support uint64 so casting it to an int64 would probably wrap the value into negative space or error. Int 64 max is 19 characters, so more than 18 characters gets us close and the only transactions I could find today was int64 max which is why I excluded that explicitly.