SQL:是否可以使用INTERVAL类型的SUM()字段? [英] SQL: Is it possible to SUM() fields of INTERVAL type?

问题描述

我正在尝试对INTERVAL求和.例如

SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL

是否可以编写一个在Oracle和SQL Server上都可以使用的查询?如果可以，怎么办?

将DATE更改为INTERVAL

解决方案

好吧，经过一番苦难之后，借助stackoverflowers的答案，我找到了适合自己需求的解决方案.

SELECT
  SUM(CAST((DATE1 + 0) - (DATE2 + 0) AS FLOAT) AS SUM_TURNAROUND
FROM MY_BEAUTIFUL_TABLE
GROUP BY YOUR_CHOSEN_COLUMN

这将返回一个浮点数(对我来说完全没问题)，它代表了Oracle ant SQL Server上的两天.

我对两个DATE添加零的原因是，在我的情况下，Oracle DB上的日期列为TIMESTAMP类型，而SQL Server上的日期列为DATETIME类型(这很奇怪).因此，在Oracle的TIMESTAMP上添加零就像在转换日期一样，并且对SQL Server DATETIME类型没有任何影响.

谢谢你们！你真的很有帮助.

I am trying to sum INTERVAL. E.g.

SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL

Is it possible to write a query that would work both on Oracle and SQL Server? If so, how?

Edit: changed DATE to INTERVAL

解决方案

Ok, after a bit of hell, with the help of the stackoverflowers' answers I've found the solution that fits my needs.

SELECT
  SUM(CAST((DATE1 + 0) - (DATE2 + 0) AS FLOAT) AS SUM_TURNAROUND
FROM MY_BEAUTIFUL_TABLE
GROUP BY YOUR_CHOSEN_COLUMN

This returns a float (which is totally fine for me) that represents days both on Oracle ant SQL Server.

The reason I added zero to both DATEs is because in my case date columns on Oracle DB are of TIMESTAMP type and on SQL Server are of DATETIME type (which is obviously weird). So adding zero to TIMESTAMP on Oracle works just like casting to date and it does not have any effect on SQL Server DATETIME type.

Thank you guys! You were really helpful.

这篇关于SQL:是否可以使用INTERVAL类型的SUM()字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！