Oracle: subtract millisecond from a datetime

Question

I thought it was really simple but it isn't.

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY')            - 1/(24*50*60*1000) data  FROM dual; 

It simply doesn't work.

---

Other details:

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') -            NUMTODSINTERVAL(1/(24*50*60*1000),'HOUR') data  FROM dual; 

doesn't work..

The right seems to be

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') -            NUMTODSINTERVAL(1/(24*25*60*1000),'HOUR') data  FROM dual; 

Why? How does it work?

Answer 1

For adding or subtracting an amount of time expressed as a literal you can use INTERVAL.

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY')      - INTERVAL '0.001' SECOND  FROM dual; 

As well there are now standard ways to express date and time literals and avoid the use of various database specific conversion functions.

SELECT TIMESTAMP '2012-10-08 00:00:00'     - INTERVAL '0.001' SECOND DATA FROM dual; 

For your original question the time part of a day is stored in fractional days. So one second is:

1 / (hours in day * minutes in hour * seconds in a minute) 

Divide by 1000 to get milliseconds.

1 / (24 * 60 * 60 * 1000) 

Answer 2

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - NUMTODSINTERVAL(1/(24*50*60*1000),'HOUR') data  FROM dual; 

OUTPUT

DATA                              --------------------------------- 09/AUG/12 11:59:59.999950000 PM    1 row selected.