pgp44

with recursive inputparams as (
  select 260 as BucketSize
),
...
timeseries as (
  select * from (
    select '2019–04–01 23:59:00'::timestamp, 3504
    union select '2019–04–01 23:58:00'::timestamp, 3488
    ...
  ) tab(timestamp,value)

pgp44

select
  to_timestamp(((t.p).p2::point_t).x) at time zone 'utc',
  ((t.p).p2::point_t).y
from largesttriangle t
where ((t.p).p2::point_t).y != 0
order by 1

pgp44

largesttriangle(grp,p) as (

  select
    wga.grp,
    ((0,0),(wga.x,wga.y),(0,0),0.0)::triangle_t
  from withgrpavg wga
  where grp = 1
  
  union all
  

pgp44

1, timestamp,value,avg timestamp bucket 2,avg value bucket 2
...
2, timestamp,value,avg timestamp bucket 3,avg value bucket 3
2, timestamp,value,avg timestamp bucket 3,avg value bucket 3
...
3, timestamp,value,avg timestamp bucket 4,avg value bucket 4
3, timestamp,value,avg timestamp bucket 4,avg value bucket 4
3, timestamp,value,avg timestamp bucket 4,avg value bucket 4
...
n, timestamp,value,null,null

pgp44

-- First and last (timestamp,value) of the timeseries
tsrange as (
  select
    (select (extract(epoch from timestamp), value)::point_t
     from timeseries order by timestamp asc limit 1) as frst,
    (select (extract(epoch from timestamp), value)::point_t
  from timeseries order by timestamp desc limit 1) as lst
),

-- Add bucket number (grp) for all but the last bucket

pgp44

timeseries as (
  select … as timestamp, … as value from … where …
),

pgp44

with recursive inputparams as (
  select 3600 as BucketSize
),

pgp44

select largest_triangle(p1,p2,p3)
  from ( select (0,0)::point_t, (1,1)::point_t, (0,1)::point_t
     union select (0,0)::point_t, (2,2)::point_t, (0,2)::point_t 
     union select (0,0)::point_t, (4,4)::point_t, (0,4)::point_t
     union select (0,0)::point_t, (3,3)::point_t, (0,3)::point_t
  ) triangles(p1,p2,p3)
⇒ ((0,0),(4,4),(0,4),8)

pgp44

create function triangle_surface(p1 point_t,p2 point_t,p3 point_t)
returns float
language SQL
as $$
   select abs(p1.x*(p2.y-p3.y)+p2.x*(p3.y-p1.y)+p3.x*(p1.y-p2.y))/2
$$;

pgp44

create aggregate largest_triangle (point_t,point_t,point_t) (
 stype = triangle_t,
 sfunc = largest_triangle_accum
);

create function largest_triangle_accum (maxsurfacetriangle triangle_t, p1 point_t, p2 point_t, p3 point_t)
returns triangle_t
language SQL
as $$
select