Oracle Timestamp vs. Date

Recently have been hitting a brick wall due to a bug at work.

Query doesn't return any results if the date is "2009-06-01 00:00:00:01". But for the date "2009-06-01 00:00:00:00" it works fine.

The table is as follows:

And contains:

ID	CODE	STARTDATE	ENDDATE
1	0358	1900/01/01 00:00:00:000000	(null)

/* Formatted on 28/11/2013 17:02:58 (QP5 v5.227.12220.39754) */
SELECT *
FROM MUNICIPAL municipal1_  
WHERE municipal1_.code='0358'
AND (TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2') BETWEEN municipal1_.startdate
AND NVL (municipal1_.enddate - 1, TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2')))

The query above returns no rows. Why?

Some notes:

1. the (null) for enddate means the timeperiod reaches into the future
2. the sql function BETWEEN^[5] checks to see if a value is within a certain range. The boundaries are inclusive.
3. The enddate is a timestamp(6)
4. enddate - 1 is no longer a timestamp, but a common date. An implicit datatype conversion takes place.^[4]
5. date doesn't store anything smaller than seconds ^[2]
6. we lose the milliseconds
7. NVL tries, if the first argument is null, to cast the second argument to the same type as the first argument (a date) ^[3]
8. even though enddate is null, the data conversion still takes place
9. ergo, the timestamp used now falls outside the range, and no results are returned.

Solution

There is one good solution to this. Don't use BETWEEN with timestamps if the range is exclusive the enddate. Use greater than/equals (>=) and lesser than (<) instead.

If you do wish to change a TIMESTAMP, use INTERVAL instead of arithmetic. ^[4]

/* Formatted on 28/11/2013 17:02:58 (QP5 v5.227.12220.39754) */
SELECT *
FROM MUNICIPAL municipal1_  
WHERE municipal1_.code='0358'
AND (TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2') >= municipal1_.startdate
AND TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2') < nvl(municipal1_.enddate, 
                            TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2')))

Conclusion

Do NOT use Arithmetic with Timestamps! You will get an automatic conversion to datatype Date and lose millisecond precision!

It is quite ugly to substract a day from a DATE, just so that you could use BETWEEN and not worry about the last boundary being included.

I guess it is my own fault for appropriating a bit of SQL code that was originally designed for DATES instead.

References

[1] Timestamp

http://docs.oracle.com/javase/7/docs/api/java/sql/Timestamp.html

[2] PL/SQL Data Type

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/datatypes.htm#CIHBCJEG

[3] NVL

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

[4] Math with Timestamp

https://blogs.oracle.com/knutvatsendvik/entry/math_with_timestamp

[5] BETWEEN

http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm#SQLRF52147