> select... from table where [9,xdate::date]::daterange @:> datecolumn and metric = 'measure you want' order by date
desclimit 1
In the correlated subquery a simple select date, value from metrics
where metric_id=x order by datetime desc limit 1 would get you the
figure you want. but of course this has to be repeated for every
metric you collect and if you wanted to do something fancy like plot
some measure over a year based on monthly figures it's massively
inefficient.
> Back to the design of your db, it sounds like you have a fixed set of metrics you are recording. If this is so I
thinkmaking them distinct columns in your data set is preferable than the scheme you are using. But if you are going to
beadding many new metrics frequently then your design makes sense to me.
The problem with this is that most of the rows will only have one
column filled in. In fact probably all of them are.
I am starting to think the best way to handle this is by creating some
time slot tables with all the columns and populating with batch jobs.
A daily table, weekly table, monthly table etc. Another option might
be to go ahead and write that slow and io heavy query and save it as a
materialized view.