Unlike other time functions, this returns the same answer for 24 hours so it seems strange it prevents query caching. Is this a bug or expected behaviour?
I want to save a view with a fixed rolling date window on a date partitioned table (yesterday to 8 days ago), but this isn't possible if I need to construct the date outside of bigquery to ensure caching.