Skip to content
Advertisement

ORA-01873 when calculating the difference between two timestamps

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.

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!!

10 People found this is helpful
Advertisement