Search code examples
sqloracle19c

How to extract lines created or modified since the last hour

I have the following code where DAT_CRE is for creation date and DAT_MOD for modification date (which can be null if no modification has been done).

I would like to be able to add to this script a condition to extract the lines created / modified in the previous hour but I am not sure how to do it with SYSDATE.

SELECT
    CODE_ART_COM,
    LIB_ART_COM,
    TO_CHAR(
        CAST(
            COALESCE(GREATEST(DAT_CRE, DAT_MOD), DAT_CRE) AS TIMESTAMP
        ),
        'YYYY-MM-DD HH24:MI:SSxFF'
    ) TIMESTAMP
FROM
    ART_COM
WHERE
    ETAT = 0
    AND CODE_ART_COM IN (
        SELECT
            CODE_ART_COM
        FROM
            PROD_COM
        WHERE
            ETAT = 0
    )
Solution

  • Simply add

    AND (dat_cre >= SYSDATE - INTERVAL '1' HOUR OR
     dat_mod >= SYSDATE - INTERVAL '1' HOUR)

    to your query in order to restrict it to rows created or updated in the last hour.