What does INTERVAL ‘182’ DAY(3) mean in SQL / Oracle?

Trying to understand INTERVAL and DAY(3) usage in SQL, as I’ve come across it and need to understand what it means. It’s in a WHERE clause, seen below

WHERE POSTING_DATE > CURRENT_DATE - INTERVAL '182' DAY(3)

Help needed, thanks!

Answer

From the documentation:

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.

Specify this data type as follows:

INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]

where

day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
…

So INTERVAL '182' DAY(3) defines an interval of 182 days. You need to specify the (3) part because the default precision of 2 doesn’t allow more than 99 days; without the precision override it will error:

select INTERVAL '182' DAY from dual;

ORA-01873: the leading precision of the interval is too small

but with it you get a valid value:

select INTERVAL '182' DAY(3) from dual;

INTERVAL'182'DAY(3)
-------------------
+182 00:00:00

And the calculation CURRENT_DATE - INTERVAL '182' DAY(3) will give you current time (according to your session time zone, since you’re using current_date not sysdate) 182 days ago. Your query will find rows where POSTING_DATE is more recent than 182 days ago.