I am trying to produce the number of days between two timestamps, as a precise fractional number.
My code is producing
ORA-01873: the leading precision of the interval is too small
This single line seems to be the culprit.
When I remove this one line, the query starts working again.
SELECT ... EXTRACT(DAY FROM 24*60*60*(ssts.TIMESTAMP - MAX(pssts.TIMESTAMP)))/(24*60*60) AS DAYS_SINCE_PREV_TIMESTAMP, ... FROM ...
What is wrong with it?
It was working fine last week.
The difference between the two timestamps will produce interval which is the day to second.
If you want the precise fractional number then you can achieve it using date datatype.
Select ... cast(ssts.TIMESTAMP as date) - cast(MAX(pssts.TIMESTAMP) as date) ...
See this db<>fiddle demo of difference in timestamp and dates.