Querying Time Series in PostgreSQL

http://no0p.github.io/postgresql/2014/05/08/timeseries-tips-pg.html

by johnhess • 12 years ago

111 7 12 years ago

no0p.github.io

AlisdairO2• 12 years ago

Apologies for the self-plug, but you can have a play around with some of PG's date/time functionality by going to http://pgexercises.com/questions/date/

tofflos• 12 years ago

I liked the puzzles. I've mostly been looking at the date category. Could you mention that a slot is 30 minutes somewhere in the question at http://pgexercises.com/questions/date/endtimes.html?

AlisdairO2• 12 years ago

Good idea - I'll add it to my to-do list :-)

afiedler• 12 years ago

This is pretty cool, I didn't know about window functions in Postgres. Most of the time when dealing with time series data I end up dropping into Python (pandas) or R (xts package), but it is nice to see how to do a lot of analysis right in the database.

alexchamberlain• 12 years ago

Really interesting read. Anyone got any comparison between the performance of these against say pandas or R[1]? I'd be particularly interested if anyone has stored multiple timeseries in a table and the performance of looking for individual or the latest timeseries.

[1]: I haven't used R myself.

rgraham• 12 years ago

I don't have as much experience with pandas, but I've used window functions and psql queries in place of that sort of data munging for production web applications. We have tens of millions of rows and near real time reporting requirements.

Reach out if you want to compare notes.

nickporter• 12 years ago

Heh, some of the queries we do at work are pretty much exactly like the ones in the examples. Postgres is cool.

Querying Time Series in PostgreSQL

Related Stories