Vertica与PostgreSQL的计算方法不同

时间:2022-09-06 13:30:04

I have one query:

我有一个问题:

SELECT CAST(((stats.ts_spawn - 1427835600) / 86400) * 86400 + 
1427835600 AS INTEGER) AS anon_1 FROM stats WHERE stats.ts_spawn > 
1427835600 AND stats.ts_spawn < 1428440399 GROUP BY anon_1 order by anon_1;

I'm expecting to get start of the each day in a week.

我希望能在一周内开始每一天。

Result in Postgresql:

结果在Postgresql中:

1427835600
1427922000
1428008400
1428094800
1428181200
1428267600
1428354000

Vertica returns start of each hour of each day of the week:

Vertica返回一周中每天每小时的开始:

1427839200
1427842800
1427846400
1427850000
... and so on, total 167 records(24 * 7 - 1)

I have no idea how to modify this query.

我不知道如何修改此查询。

1 个解决方案

#1


1  

The second one is obviously resulting in a float not an integer in division. In Vertica documents we can read this:

第二个显然导致浮点不是除法中的整数。在Vertica文档中,我们可以读到:

the Vertica 6 release introduced a behavior change when dividing integers using the / operator

使用/运算符分割整数时,Vertica 6版本引入了行为更改

If you want the query to behave the same on both systems either change the configuration option as mentioned in that doc or use the Floor() function on the result of division.

如果您希望查询在两个系统上的行为相同,请更改该doc中提到的配置选项,或者对division的结果使用Floor()函数。

#1


1  

The second one is obviously resulting in a float not an integer in division. In Vertica documents we can read this:

第二个显然导致浮点不是除法中的整数。在Vertica文档中,我们可以读到:

the Vertica 6 release introduced a behavior change when dividing integers using the / operator

使用/运算符分割整数时,Vertica 6版本引入了行为更改

If you want the query to behave the same on both systems either change the configuration option as mentioned in that doc or use the Floor() function on the result of division.

如果您希望查询在两个系统上的行为相同,请更改该doc中提到的配置选项,或者对division的结果使用Floor()函数。