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?

@Thinhbk:He wanted millisecond to be subtracted from that timestamp,if you look to the query above ,this will output you only `10/AUG/12`,and actually he wanted `09/AUG/12 11:59:59.999950000 PM ` as output .`Gik25` correct me if i am wrong . — Gaurav Soni, Aug 10 '12 at 11:27

Brian · Accepted Answer · 2012-09-07T00:43:36.220

32

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)

edited Sep 07 '12 at 00:43

answered Sep 07 '12 at 00:23

Brian

6,717
2
23
31

2

+1 Interval literals are definitely the way to go. Format models and date multiplication are just bugs waiting to happen. – Jon Heller Sep 07 '12 at 04:10

score 11 · Answer 2 · edited Feb 02 '15 at 09:47

11

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.

edited Feb 02 '15 at 09:47

AlikElzin-kilaka

34,335
35
194
277

answered Aug 10 '12 at 11:21

Gaurav Soni

6,278
9
52
72

How do you simulate the OUTPUT? – Revious Aug 10 '12 at 11:27
It didn't work.. Correct seems to be: SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - NUMTODSINTERVAL(1/(24*25*60*1000),'HOUR') data FROM dual; Why? I don't understand.. – Revious Sep 06 '12 at 11:05
Not working. `SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - NUMTODSINTERVAL(1/(24*50*60*1000),'HOUR') data FROM dual;` gives the error: *FROM keyword not found where expected* – AlikElzin-kilaka Jan 28 '15 at 14:34
1

@AlikElzin-kilaka:It works for me ,try to align the text in one line ,its strange but this is how it is working :) – Gaurav Soni Feb 02 '15 at 06:28

score 9 · Answer 3 · answered Sep 06 '12 at 12:54

The answer posted above subtracts a tenth of a millisecond from the date. I think what you want is the following:

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY')-NUMTODSINTERVAL(1/1000,'SECOND')
  FROM dual;

Output:

DATA
---------------------------------------------------------------------------
09-AUG-12 11.59.59.999000000 PM
                   ^^^
                   |||
              tenths|thousandths
                    |
                hundredths

The following NUMTODSINTERVAL(1/(24*25*60*1000),'HOUR') seems to work only because 24*25 = 600. But that number is wrong because 1/(600*60*1000) of an hour is a tenth of a millisecond, not a millisecond. If you want to use 'HOUR' in NUMTODSINTERVAL() you should use 1/(60*60*1000) (sixty minutes in an hour, sixty seconds in a minute, 1000 ms in a second).

score 7 · Answer 4 · 2012-09-06T12:52:59.060

This is correct (with a millisecond being 1000th of a second):-

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - NUMTODSINTERVAL(1/1000,'SECOND') data FROM dual;


DATA
-----------------------------
09-AUG-12 23.59.59.999000000

As to why the other code isn't working it's because you aren't calculating a millisecond correctly. An hour must be divided by 60 to give minutes and again by 60 to given seconds then by 1000 to give a millisecond, thus if you must use HOUR as the interval then it is:-

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

DATA
---------------------------------------------------------------------------
09-AUG-12 23.59.59.999000000

Nick Krasnov · Answer 5 · 2012-09-06T14:13:50.830

3

select TO_CHAR(TO_TIMESTAMP('10.05.2012', 'DD.MM.YYYY') - 
       NUMTODSINTERVAL(1/1000, 'SECOND'), 'DD.MM.YYYY HH24:MI:SS:FF3')  Res 
  from dual;

RES
-----------------------------
09.05.2012 23:59:59.999

edited Sep 06 '12 at 14:13

answered Sep 06 '12 at 13:57

Nick Krasnov

26,886
6
61
78

Oracle: subtract millisecond from a datetime

5 Answers5