CODEWITHSUNDEEP
oracle-database
Anish Gopinath
Anish Gopinath

Reputation: 182

how to fetch exact record from the 24th hour in oracle database

First of all, sorry for this redundant question. There are tons of result available for my question, but it is not working for me. Can someone help on the below scenario?

For reference purpose, i have included the column timestamp(col_timestamp) and the 24hour before from the timestamp(crnt_time_before_24hr) and current_timestamp(crnt_time)

SELECT
    TO_CHAR(delete_requested_dt,'YYYY-mm-dd hh24:mi:ss') as col_timestamp,
    TO_CHAR(systimestamp - interval '24' hour,'YYYY-mm-dd hh24:mi:ss') as crnt_time_before_24hr,
    TO_CHAR(systimestamp,'YYYY-mm-dd hh24:mi:ss') as crnt_time

FROM
    test_user_table
WHERE
        status_id IN (
            801,802
        )
    AND
        trunc(delete_requested_dt) <=  sysdate - interval '1' day
ORDER BY delete_requested_dt desc;

The above query is totally opposite of my objective. This query will check the values which are outside the 24 hours from the current date. I used this query to check if there are any values, which was supposed to be within the 24 hour from the current date are present or not!!

below are the results

COL_TIMESTAMP       CRNT_TIME_BEFORE_24 CRNT_TIME          
------------------- ------------------- -------------------
2019-02-26 23:55:57 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:55:46 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:55:38 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:55:25 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:55:14 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:55:01 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:54:51 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:54:40 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:54:29 2019-02-26 13:06:30 2019-02-27 13:06:30
2019-02-26 23:23:40 2019-02-26 13:06:30 2019-02-27 13:06:30

As you can see from the output the value which is in 24hour from the current date is 2019-02-26 13:06: 30.

The date value from the table on the LHS( COL_TIMESTAMP) shows the data is 2019-02-26 23:23:40.!!!!

How is it even possible? only the values which is less than 2019-02-26 13:06:30 should come right!!! Am i missing something? please help me.

FYI:delete_requested_dt column is timestamp datatype

Upvotes: 0

Views: 118

Answers (1)

OracleDev
OracleDev

Reputation: 535

Try this:

SELECT
    TO_CHAR(delete_requested_dt,'YYYY-mm-dd hh24:mi:ss') as col_timestamp,
    TO_CHAR(systimestamp - interval '24' hour,'YYYY-mm-dd hh24:mi:ss') as crnt_time_before_24hr,
    TO_CHAR(systimestamp,'YYYY-mm-dd hh24:mi:ss') as crnt_time

FROM
    test_user_table
WHERE
        status_id IN (
            801,802
        )
    AND
        cast(delete_requested_dt as date) <=  sysdate - interval '1' day
ORDER BY delete_requested_dt desc;

Upvotes: 1

Related Questions