Timestamp in Oracle

Question

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.

Answer 1

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

Answer 2

Simple:

Your first record --> '07-DEC-23 12.01.55.000000000 AM' --> 12 in the morning. Starting of the day --> 00 in 24 hour format

In your condition --> TO_TIMESTAMP('07-12-23 12:00:50.000000', 'DD-MM-YY HH24:MI:SS.FF') --> 12 in the noon --> 12 PM --> 12 in 24 hour format.

Convert your query to following to omit the first record:

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 00:00:50.000000', 'DD-MM-YY HH24:MI:SS.FF')