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.
x
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.
Advertisement
Answer
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.
Cheers!!