Need to pull records from previous full hour regardless of time the query runs

Question

I want to pull records from the previous Full hour from when the query runs.

By this I mean regardless if it runs at 2:05 or 2:15 I will get records from 1:00:00 to 1:59:59.

Currently I get ORA-00907: missing right parenthesis error even though there are correct amount of parenthesis as far as I can tell. What am I doing wrong?

WHERE ((SS.NSAMPLE < SS.LSL OR SS.NSAMPLE > SS.USL) OR SS.FAILED = 'Y')
AND SD.DIMENSION_ID = SS.DIMENSION_ID
AND SUBDATE(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL 1 HOUR) <= SS.SAMPLE_DATE AND SS.SAMPLE_DATE < STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H')

I also tried this and get the same missing right parenthesis error.

SUBDATE(SUBDATE(SUBDATE(SUBDATE(NOW(),
        INTERVAL EXTRACT(MICROSECOND FROM NOW()) MICROSECOND),
        INTERVAL EXTRACT(SECOND FROM NOW()) SECOND),
        INTERVAL EXTRACT(MINUTE FROM NOW()) MINUTE),
        INTERVAL 1 HOUR) <= SS.SAMPLE_DATE
    AND SS.SAMPLE_DATE < SUBDATE(SUBDATE(SUBDATE(NOW(),
        INTERVAL EXTRACT(MICROSECOND FROM NOW()) MICROSECOND),
        INTERVAL EXTRACT(SECOND FROM NOW()) SECOND),
        INTERVAL EXTRACT(MINUTE FROM NOW()) MINUTE)

Answer 1

You're using MySQL functions against an Oracle database. They are different database products, which both happen to be owned by Oracle Corp. (and have their documentation on docs.oracle.com), and the syntax varies. You might find the Oracle SQL Language Reference helpful.

To get data from the preceding hour in Oracle you can do:

AND SS.SAMPLE_DATE >= TRUNC(SYSDATE, 'HH') - INTERVAL '1' HOUR
AND SS.SAMPLE_DATE < TRUNC(SYSDATE, 'HH')

• SYSDATE gives you the system time, e.g. 2022-12-21 17:30:37
• TRUNC(SYSDATE, 'HH') gives you the system time truncated to the hour, e.g. 2022-12-21 17:00:00
• TRUNC(SYSDATE, 'HH') - INTERVAL '1' HOUR gives you the system time truncated to the hour, with an hour subtracted, e.g. 2022-12-21 16:00:00

The two filter conditions then look for values greater than or equal to the previous hour, and less than (but not including) this hour, so effectively for that example system time:

AND SS.SAMPLE_DATE >= timestamp '2022-12-21 16:00:00'
AND SS.SAMPLE_DATE <  timestamp '2022-12-21 17:00:00'

which covers all of the possible times in that hour-long period.

fiddle

---

The "ORA-00907: missing right parenthesis" error doesn't always mean you have the wrong number of parentheses. It means the parser got to a point where the next thing it expected to see was a right parenthesis, and it saw something else instead. It can have a variety of sometime non-obvious underlying problems.

In this case it's actually the interval syntax that's confusing it - if you change INTERVAL 1 HOUR to INTERVAL '1' HOUR to make it a valid Oracle interval literal then the error will change to "ORA-00904: "STR_TO_DATE": invalid identifier", which is easier to understand, as that's one of the MySQL functions that doesn't exist in Oracle.