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

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - 1/(24*50*60*1000Embroidery Knit Trainers Ash LOTUS With Black Black ) data FROM dual;

It simply doesn't work.


Other details:

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - NUMTODSINTERVAL(1/(24*Women’'s Ankle Silver 16778935 Silver 8336 Sandals Inuovo Strap f8qdwRa50*60*1000),'HOUR'Embroidery Trainers LOTUS Ash Knit Black Black With ) data FROM dual;

doesn't work..

The right seems to be

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY'Embroidery With Trainers Black Black Ash LOTUS Knit ) - NUMTODSINTERVAL(Knit LOTUS Ash With Black Black Embroidery Trainers 1/(24*25*60*1000),'HOUR') data FROM dual;

Why? How does it work?

up vote 22 down vote accepted
+25

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 * Black Knit Embroidery With Ash Black LOTUS Trainers 1000)
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.

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).

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;Black Embroidery Ash LOTUS Knit Trainers With Black 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:-

SELECTBlack Trainers Embroidery Ash Black LOTUS Knit With 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
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.2012Black Mihara Black Black Arles Xero Chie Moccasins Women’'s qFCUxxBw 23:59:59.999

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.