Re: Querying sporadic time series type data.

Couldn't he also use a daterange (or tsrange) in the where clause?
Something like:

select... from table where [9,xdate::date]::daterange @:> datecolumn and
metric = 'measure you want' order by date desc limit 1

Even with queries of this form, based on how you describe your data I might
make two stored procedures. One where you pass in the date, the metric of
interest and it returns the single value. Then the second procedure would
take the same input and then does the averaging you want. Then you can call
them all in a single select query:

select singlemetric('date'::date, bloodpressure), avgmetric('date'::date,
insulin)...

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 think making them distinct
columns in your data set is preferable than the scheme you are using. But
if you are going to be adding many new metrics frequently then your design
makes sense to me.

On Sat, Oct 3, 2020 at 5:10 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> The convention here is to inline or bottom-post.
>
> On Saturday, October 3, 2020, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
>
>> Given the scale I am working at I thought a specific time scale
>> database would be overkill but I'll look into it nevertheless. Even
>> if I do write the queries with the known metrics I am still trying to
>> figure out how to efficiently query "last known value of this metric
>> on or before X time" without a correlated subquery which would be a
>> massively inefficient query.
>>
>
> Yes, if the time is arbitrary you are stuck with a where clause,
> sort-and-limit. DISTINCT ON can be useful for returning multiple entities
> over the same metric.
>
> Indexes, possibly partial (if you don’t normalize the model), should help.
>
> Even massively inefficient can be efficient enough...
>
> David J.
>
>