When the TIME_BUCKET function is used for timestamp with time zone, there may be a timestamp overflow issue resulting in error ORA-01877 due to the time zone conversion in the TIME_BUCKET function calculation. For example:

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='syyyy-mm-dd hh24:mi:ss tzr tzd';
Session altered.
SQL> SELECT TIME_BUCKET(TIMESTAMP '9999-12-15 10:00:00 +02:00', 'P1M', TIMESTAMP '9999-08-01 01:30:00 +5:00', END) AS tmbkt_res;

ORA-01877: string is too long for internal buffer
no rows selected

For the TIME_BUCKET function for timestamp with time zone, the timestamp with time zone is converted into UTC, all calculations are done in UTC, and then the result in UTC is converted into the ORIGIN time zone. The error is raised in the last step when converting the result in UTC into the ORIGIN time zone. This is an existing timestamp with time zone conversion issue. See the query below that raises the same error:

SQL> SELECT TIMESTAMP '9999-12-31 20:30:00 +00:00' AT TIME ZONE '+5:00';

ORA-01877: string is too long for internal buffer
no rows selected

Workaround

None.