Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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")
)
)
@leighmcculloch

This comment has been minimized.

Copy link
Owner Author

@leighmcculloch leighmcculloch commented Apr 19, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment