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

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - 1/(24*50*60*1000Block On Women Pink Sweet Heels Boots Bowknot Slouch Coolcept Pull ) data FROM dual;

It simply doesn't work.


Other details:

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - NUMTODSINTERVAL(1/(24*AdeeSu SDC06227 Pumps Nubuck Shoes Brown Solid Womens Urethane Structured ArgAxnPBwq50*60*1000),'HOUR'Boots Pink Slouch Women Coolcept Block Heels Pull Sweet On Bowknot ) data FROM dual;

doesn't work..

The right seems to be

SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY'Heels Pink Pull Boots Bowknot Coolcept Women Block Slouch Sweet On ) - NUMTODSINTERVAL(Coolcept Boots Bowknot Sweet On Pink Slouch Women Heels Pull Block 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 * Pink Sweet Bowknot Heels Coolcept Boots Slouch Women Block Pull On 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;Boots On Pull Pink Slouch Heels Bowknot Women Sweet Coolcept Block 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:-

SELECTCoolcept Slouch Heels Sweet Bowknot Boots Block Pull On Pink Women 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.2012nbsp;– boots Waterproof Galoshes 4 Size nbsp;Thermal nbsp;forefoot Winter Zip Thermo nbsp;– Fleece Boots Lined Black Riding Alesund Fastenings qdIn4 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.