Using INTERVAL for time operations in SQL

Sometimes when you are set in your ways you miss out on little things that can make a life a bit easier.

For a long time if I needed SQL to find changes that occurred in the last 5 minutes on a table that had some sort of time column I would do something like this

SELECT * 
FROM example_log 
WHERE updated_dt_time >= SYSDATE - 5/60/24
ORDER BY updated_dt_time desc

Using the calculation 5/60/24 to work out what fraction 5 minutes is as part of a day. (I guess if you knew you could say 0.003472..)

Recently someone was showing me a query and did the equivalent of this

SELECT * 
FROM example_log 
WHERE updated_dt_time >= SYSDATE - INTERVAL '5' MINUTE 
ORDER BY updated_dt_time desc

Which has the same result. I hadn't even come across the INTERVAL concept before. So much easier to read and work with. There is a lot more to it as usual (DAY, HOUR, options etc) and you can find reference at http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51067