Why am I seeing a "query size limitation" warning when filtering on one particular hour in my report?
Analytics, Sites which utilize Daylight Savings Time (DST)
Oracle B2C Service, All versions
A report is unable to pull data from a very small timeframe beginning or ending between 02:00 & 02:59:59, even though it works for other times. The filter will then fall back to the oldest date it can: 12-31-1969 06:59 PM.
Daylight Savings Time (DST) is the practice of setting the clock forward by one hour during the warmer part of the year so that evenings have more daylight and mornings have less. DST starts at 2:00 am local time and DST will move the clocks forward one hour. Local time between 02:00 - 02:59:59 does not exist during the transition from standard time to DST. This hour is skipped, therefore this day only consists of 23 hours.
Therefore, any report filter explicitly using any time from 02:00 - 02:59:59 on a DST start date may fail. SQL determines an "incorrect datetime value" was used. This filter is then ignored, which may cause a max_join error on the database level. This will present itself as a warning:
This report could not be processed because it exceeds the query size limitation. Please reduce the amount of data processed by the report.
To resolve this issue, change the filter to a time that actually happened. I.E., 01:59 or 03:00.
The End of DST - The opposite happens in the autumn, and there will be a 25 hour day with the 01:00 hour repeating itself thus: 01:59:58, 01:59:59, 01:00:00, 01:00:01...