Oracle: Subtract a millisecond from a date

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?

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)
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Oracle: Subtract a millisecond from a date

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录