Oracle SQL - using SYSDATE to display

Here is the table:

Col1    Col2    Col3
---------------------------------------
4567    102     5/5/2011 3:37:41 PM
9876  开发者_Python百科  102     5/5/2011 3:37:43 PM
1234    104     5/5/2011 12:46:16 PM
9999    104     5/5/2011 12:47:09 PM
9999    104     5/5/2011 12:47:15 PM

How can I use the SYSDATE to fetch only one or more record(s) using rapid display on a report via seconds 'SS' and/or minutes 'MI'? I know about BETWEEN fxn, but dealing with seconds, it's kind of slippery slope to me. Any ideas?

Not quite sure what you mean, but if you want to restrict returned records to a few minutes or seconds old, and assuming COL3 is indexed, you can do something like this:

SELECT COL1, COL2, COL3
FROM MY_TABLE
WHERE COL3 > SYSDATE - INTERVAL '3' MINUTE
ORDER BY COL3 DESC;

INTERVAL can be in seconds if you prefer; or both, e.g. INTERVAL '01:30' MINUTE TO SECOND would go back 90 seconds, as would INTERVAL '90' SECOND. (Also not sure if this needs to be supplied by the user as a parameter).

Still unclear what you are trying to achieve and what criteria you want to use for restricting the returned rows. But if you do want the most recent record and any that fall within a specific period of it, you can do something like this:

SELECT COL1, COL2, COL3 FROM (
    SELECT COL1, COL2, COL3,
        NUMTODSINTERVAL(
            FIRST_VALUE(COL3) OVER (ORDER BY COL3 DESC) - COL3,'DAY') AS DIFF
    FROM MY_TABLE
)
WHERE DIFF < INTERVAL '01:30' MINUTE TO SECOND
ORDER BY COL3 DESC;

This would return the most recent record, plus any with COL3 within 90 seconds of it. So there will always be at least one record returned. But it isn't filtering COL3, it has to evaluate every row to work out the offset, so it'll probably do a full table or index scan. You may be able to filter the innner select anyway, if you only ever want records from today, for example. If not it might not meet your 'rapid' requirement.

A potentially faster, but perhaps uglier, solution might be:

SELECT COL1, COL2, COL3
FROM MY_TABLE
WHERE COL3 > (SELECT MAX(COL3) FROM MY_TABLE) - INTERVAL '01:30' MINUTE TO SECOND
ORDER BY COL3 DESC;

Really not sure this is what you want, or how SYSDATE fits in.

You can calculate with SYSDATE. Minus 1 is yesterday. Minus 1/24 is one hour ago. Minus 1/(24*60) is one minute ago. And minus 1/(24*60*60) is one second ago.

This query should show you all records with a date in the last 10 seconds:

select *
from my_table
where col3 > sysdate - 10/(24*60*60);

Is that what you're after?