Using BETWEEN or <=, >= for Oracle timestamps

Question

My Oracle table has a column tmstp with the TIMESTAMP(3) WITH TIME ZONE datatype.

When I run the following query

SELECT COUNT(column1) 
  FROM table1 
 WHERE tmstp BETWEEN to_timestamp_tz('10-OCT-10 05.00.00.000000000 AM -05:00', 'DD-MON-RR HH.MI.SSXFF AM TZR')
                 AND to_timestamp_tz('11-OCT-10 04.59.59.999999999 AM -05:00', 'DD-MON-RR HH.MI.SSXFF AM TZR');

it gives about twice the count as the following query:

SELECT COUNT(column1) 
  FROM table1 
 WHERE tmstp >= to_timestamp_tz('10-OCT-10 05.00.00.000000000 AM -05:00', 'DD-MON-RR HH.MI.SSXFF AM TZR') 
   AND tmstp <= to_timestamp_tz('11-OCT-10 04.59.59.999999999 AM  -05:00', 'DD-MON-RR HH.MI.SSXFF AM TZR');

I thought they should give equivalent results. Any input on what might be happening? How should I debug this?

Answer 1

Sorry for the trouble. I figured it out. My question is actually flawed too because I edited it to hide confidential data.

There was an extra whitespace between the AM and timezone offset in one of the queries and that caused different results.

Stupid me.

So if the timestamp string that is converted is a bit off with whitespaces, the answers might be off.