Vertica different calculations than in PostgreSQL
NickName:Sleepwalker Ask DateTime:2015-09-09T21:07:40

Vertica different calculations than in PostgreSQL

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:

1427835600
1427922000
1428008400
1428094800
1428181200
1428267600
1428354000

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

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

I have no idea how to modify this query.

Copyright Notice:Content Author:「Sleepwalker」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/32480429/vertica-different-calculations-than-in-postgresql

More about “Vertica different calculations than in PostgreSQL” related questions

Vertica different calculations than in PostgreSQL

I have one query: SELECT CAST(((stats.ts_spawn - 1427835600) / 86400) * 86400 + 1427835600 AS INTEGER) AS anon_1 FROM stats WHERE stats.ts_spawn &gt; 1427835600 AND stats.ts_spawn &lt; 1428440399

Show Detail

Is there any SQL support in Vertica/MySQL for linear algebra calculations?

My data is already in Vertica and MySQL. I need to calculate many linear algebra related calculations - cross products, matrix multiplications and some other related calculations you can easily per...

Show Detail

trouble with moving data from PostgreSQL to Vertica

I want to move some tables from PostgreSQL to Vertica, but having trouble with saving column's types as they were. After moving table to Vertica all my columns' types are VARCHAR. To copy table from

Show Detail

write UUID in Vertica with jooQ

I don't have jOOQ generated classes, so, I want to use my class and write it to vertica. Table&lt;Record&gt; table = DSL.table(DATA_TABLE_NAME); for (Data d : data) { dsl.

Show Detail

Convert String to array and validate size on Vertica

I need to execute a SQL query, which converts a String column to a Array and then validate the size of that array I was able to do it easily with postgresql: e.g. select cardinality(string_to_arr...

Show Detail

Vertica PlannedConcurrency

I have been trying to tune the performance of queries running on a Vertica cluster by changing the value of PlannedConcurrency of the general resource pool. We have a cluster of 4 nodes with 32 cores/

Show Detail

How to connect python with vertica using ODBC?

import sqlalchemy as sa conn = "vertica+pyodbc://dbadmin:password@VMart" sa.create_engine(conn, pool_size=10, max_overflow=20) %load_ext sql %sql vertica+pyodbc://VMart error (pyodbc.InterfaceE...

Show Detail

How to tweak the SET intervalstyle (change the Interval Output) in PostgreSQL?

I have read in this online PostgreSQL documentation... http://www.postgresql.org/docs/9.4/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE in the point 8.5.5 something about how to tweak the

Show Detail

Vertica Overall

Hi all, my company and I are looking for a product that can give a hand in taking off some loads (big once) from OLTP databases! So we have stumbled on Vertica. After some tests and studies for the...

Show Detail

Merge roles in vertica

Do you know if there is a smart way to merge different roles into one single role in HP vertica database? I mean, I have 3 roles with different grants in vertica, and I need to merge those 3 into 1...

Show Detail