Oracle：从datetime中减去毫秒 [英] Oracle: subtract millisecond from a datetime

问题描述

我以为这很简单，但不是这样。

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;

它根本不起作用。

其他详细信息：

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

不工作。

右边似乎是

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

为什么？如何工作？

推荐答案

为了添加或减少一段时间，以文字形式表达，您可以使用INTERVAL。

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)

除以1000获得毫秒。

Divide by 1000 to get milliseconds.

1 / (24 * 60 * 60 * 1000)

这篇关于Oracle：从datetime中减去毫秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！