When someone filters on a timestamp field (ie a date field) and uses the offset to say something like 10 days ago (ie -10 day) in the filter's settings this breaks the time based caching.

The reason being that DBTNG's preExecute() function, in plugins/views_plugins_cache.inc, is converting ***CURRENT_TIME*** to it's timestamp value before the cache key is determined. Hence in lieu of the the ***CURRENT_TIME*** placeholder appearing in the build_info array the timestamp appears. The end result is the cache key is different every second and therefore the cached is only valid for 1 second. :(

timestamp in the SQL string used to compute the cache key in D7/Views3

node_revision.timestamp >= 1330976131-864000

timestamp in the SQL string used to compute the cache key in D6

node_revision.timestamp >= ***CURRENT_TIME*** - 864000

the code at issue here is line 264 for the plugins/views_plugins_cache.inc

I don't know the fix right off the top of my head, though I know for the time being we're gonna overwrite the handler with our own get_results_key(0 and get_output_key. Seems to me the fix is one of two things.

1.) Get ***CURRENT_TIME*** passed as a placeholder ala :db_condition_placeholder_x in build_info['query'] (and build_info['count_query']), which would have to happen in the views_handlers_filter_date handler
2.) Find a way to get the meta data form the SelectQuery objects

One of the problems with changing the date filter to the proposed sql is that there are performance issues with running functions against fields in the where clause. this forces a table scan because the logic has to be applied to every row in the table. The better bet is going to be to use the mysql specific function: "unix_timestamp()" rather than php's request date.

luckily the node_revision.timestamp field is still using ***current_time*** see:

function op_simple($field) {$value = intval(strtotime($this->value['value'], 0)); if (!empty($this->value['type']) && $this->value['type'] == 'offset') {$value = '***CURRENT_TIME***' . sprintf('%+d', $value); // keep sign}// This is safe because we are manually scrubbing the value. // It is necessary to do it this way because $value is a formula when using an offset.$this->query->add_where_expression($this->options['group'], "$field$this->operator$value"); }?>

the problem is that the cache key is being generated after the tokens in the sql are replaced:

this has to be done this way because modules like organic groups use tokens like "***current_group***". if the query was cached before tokenization then every group would see the same information, but the revision time would work.. so its a catch 22.

you could probably get away with changing the value that the token ***current_time*** uses by declaring a better value (such as unix_timestamp() ) in a custom module that implements hook_views_query_substitutions()