Search code examples
sqloracle10gsql-timestamp

Timestamp in Oracle

I have created the below table and inserted the 2 records.

CREATE TABLE TIME_TEST(tran_data_timestamp timestamp)

insert into time_test values('07-DEC-23 12.01.55.000000000 AM');

insert into time_test values('07-DEC-23 12.01.33.000000000 AM');

while executing below query it fetches all the records.

select * 
from time_test 
where tran_data_timestamp > TO_TIMESTAMP('06-12-23 00:02:25.000000', 'DD-MM-YY HH24:MI:SS.FF') 
     and tran_data_timestamp <= TO_TIMESTAMP('07-12-23 12:00:50.000000', 'DD-MM-YY HH24:MI:SS.FF')

07-DEC-23 12.01.55.000000000 AM

07-DEC-23 12.01.33.000000000 AM

As per condition first record should not come. Please help on this.

Solution

  • You are mixing 12-hour and 24-hour times.

    You should also stop using implicit string to timestamp conversion as '07-DEC-23 12.01.55.000000000 AM' is not a timestamp, it is a string literal that happens to look like a timestamp (and Oracle tries to help by implicitly converting to a TIMESTAMP using the NLS_TIMESTAMP_FORMAT session parameter - but this can be changed by any user at any time so you should not rely on it); instead you can use TIMESTAMP literals (or explicit string-to-timestamp conversions using TO_TIMESTAMP and a format model).

    If you use the 24-hour clock (and 4-digit years) then you will see that '07-DEC-23 12.01.55.000000000 AM' is 2023-12-07 00:01:55.000000000; which is between 2023-12-06 00:02:25.000000 and 2023-12-07 12:00:50.000000.

    CREATE TABLE TIME_TEST(tran_data_timestamp timestamp)
insert into time_test values(TIMESTAMP '2023-12-07 00:01:55.000000000');
insert into time_test values(TIMESTAMP '2023-12-07 00:01:33.000000000');

    Then:

    select * 
from   time_test 
where  tran_data_timestamp > TIMESTAMP '2023-12-06 00:02:25.000000'
and    tran_data_timestamp <= TIMESTAMP '2023-12-07 12:00:50.000000'

    Outputs:

    (Assuming your are displaying the timestamp in the format 'YYYY-MM-DD HH24:MI:SS.FF')

    TRAN_DATA_TIMESTAMP
    2023-12-07 00:01:55.000000
    2023-12-07 00:01:33.000000

    So the query output is correct.

    If you want to exclude the "first" row then use an upper-bound that is lower than the "first" row's value but greater-than-or-equal to the "second" row's value, such as 2023-12-07 00:01:50:

    select * 
from   time_test 
where  tran_data_timestamp > TIMESTAMP '2023-12-06 00:02:25.000000'
and    tran_data_timestamp <= TIMESTAMP '2023-12-07 00:01:50.000000'

    Which outputs:

    TRAN_DATA_TIMESTAMP
    2023-12-07 00:01:33.000000

    fiddle