3

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.

mish15
  • 55
  • 3
  • In the meantime - how about using Apps Script to re-construct the view every 24 hours reflecting the current day in a static way? – Felipe Hoffa Oct 07 '17 at 01:37
  • 1
    Thanks @FelipeHoffa I could do that. I didn't know apps script could access bigquery! I want to hang authorised views off this view, it's an intermediate step, would be great to have it all in bigquery. Anyhow appreciate the tip – mish15 Oct 07 '17 at 04:44
  • Looks like there is a solution here: https://stackoverflow.com/a/39734030/432914 – KirylP Oct 20 '20 at 13:25

1 Answers1

2

It's to avoid having special caching logic for different functions. You could submit a feature request on the issue tracker, but I suspect that it wouldn't be a high priority relative to other issues.

Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99
  • Thanks @Elliott Interesting as I assumed those functions would be calculated before passing to the caching layer to make it more efficient. Relative dates would be useful in views (with caching). I'll file an issue. Thanks again. – mish15 Oct 07 '17 at 00:39