We have a lot of data organized by time stamp. All client queries run through a view, so we would like to use this view for a new feature. The time stamp is supposed to be converted to the user's timezone. The task looks simple for a human (just add a few hours to each time stamp) but is obviously not so easy for a database.
We could simply add the conversion to the SELECT statement of the view:
SELECT Date_as_Stored + EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP)/24 as myDate (...)
However, this leads to undesired full table scans. Most client queries have a WHERE clause involving myDate which then cannot use the index due to the calculation. The table is pretty big and the filter on time stamps is powerful, so this really hurts.
A typical client query would be
SELECT * FROM myView WHERE myDate > sysdate -1;
We don't really want to change the client queries, because there are quite a few of them. So, we cannot simply add a WHERE clause referring to Date_as_stored. The client has always used myDate and does not "know" about Date_as_Stored.
A function based index won't work, because the function is not deterministic.
We cannot use a constant offset (= deterministic), because the whole purpose of this exercise is to adapt to the user's timezone.
Looks like we have to pick one bitter pill to swallow. Or are we overlooking something?