Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

> On a non-TS oriented DBMS, this query is usually slow and hard to write.

I don't see why this is hard to write:

SELECT max(time) as most_recent_time, transaction_price INTO newtable FROM oldtable WHERE time > $my_datetime GROUP BY time;

As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica?




"most recent" is min(time) where time>=$my_datetime ;

I don't understand your query - max(time) .. GROUP BY time means that every record is its own group, so max(time)==time; also, you will have a result for every single point in time after the requested time.

There is no way in standard SQL (without window extensions) to do this with just one clause; you need the price associated with the min(time) associated with time>=$my_datetime ; You need either a subquery, a join, or some other correlated query. e.g.

SELECT time, price FROM trades WHERE time IN (SELECT min(time) FROM trades WHERE time>=$my_datetime)

And that's for one record; It gets more complicated when you try to do a batch.

> As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica?

I've timed kdb2 against Vertica a decade or so ago, and kdb2 was about x10 faster, I didn't do a proper benchmark but monetdb was in the x10-x100 slower than kdb2 at the time.


Ah yes, I should have grouped by the non-time key fields.


I still don't understand how that would give the right result.




Consider applying for YC's Fall 2025 batch! Applications are open till Aug 4

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: