XQuery表达式用来计算两个dateTimes之间的秒数是什么? [英] What would be the XQuery expression to count the number of seconds between two dateTimes?

问题描述

SQL查询:

SELECT 
    ROUND((CAST(DATEATTRIBUTE2 AS DATE) - CAST(DATEATTRIBUTE1 AS DATE)) * 86400) AS result 
FROM TEST_TABLE;

DATEATTRIBUTE1和DATEATTRIBUTE2均为TIMESTAMP类型.

我已经尝试过并提出以下 XQuery :

I have tried and come up with the following XQuery :

fn:days-from-duration(fn:subtract-dateTimes-yielding-dayTimeDuration(
     $P_REQUEST/ns1:DATEATTRIBUTE2,$P_REQUEST/ns1:DATEATTRIBUTE1))*86400

但是对于日期相同但时间不同的情况，此操作将失败.当DATEATTRIBUTE1为2017-02-23T01:17:18.0000并且DATEATTRIBUTE2为2017-02-23T01:17:20.7550时，SQL查询返回2，而XQuery返回0.

But this fails for cases when the dates are same but there is difference in time.E.g. When DATEATTRIBUTE1 is 2017-02-23T01:17:18.0000 and DATEATTRIBUTE2 is 2017-02-23T01:17:20.7550 the SQL query returns 2 while XQuery returns 0.

提前感谢您的帮助！

推荐答案

如果我正确理解，则需要两个dateTime之间的总秒数.您可以这样操作:

If I correctly understand, you need the total number of seconds between the two dateTimes. You can do it this way:

floor(
  ($P_REQUEST/ns1:DATEATTRIBUTE2 - $P_REQUEST/ns1:DATEATTRIBUTE1)
  div xs:dayTimeDuration('PT1S')
)

也就是说，减去它们-您可以使用-运算符-然后将获得的持续时间除以1s的持续时间，然后四舍五入.

That is, substracting them -- you can use the - operator -- and then dividing the obtained duration by the duration of 1s, then rounding down.

对于问题中给出的示例，其结果为2.

It yields 2 for the example given in the question.

要进一步参考，请在此处表示这种方式.

For further reference, there is a functx function documented here that suggests this way.

这篇关于XQuery表达式用来计算两个dateTimes之间的秒数是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！