0

There're two time series a(t TIMESTAMP,v REAL) and b(t TIMESTAMP,v REAL). How can I do math on two of them (e.g. a*b) WITHOUT downsampling (time bucket)?

The way I imagine is:

t = merge a.t b.t
va = interpolate a against t
vb = interpolate b against t
v = va * vb
result = (t, v)

Is that possible in timescaledb? Or is there another way that doesn't downsample?

ZisIsNotZis
  • 1,570
  • 1
  • 13
  • 30

1 Answers1

0

You can join the tables by time if you have the same DateTime.

DROP TABLE if exists a ;
DROP TABLE if exists b ;
CREATE TABLE "a" ("t" timestamp not null, "v" decimal not null);
CREATE TABLE "b" ("t" timestamp not null, "v" decimal not null);
SELECT create_hypertable('a', 't', chunk_time_interval => INTERVAL '1 day');
SELECT create_hypertable('b', 't', chunk_time_interval => INTERVAL '1 day');

insert into a VALUES ('2022-10-10 10:10:00'::timestamp, 2);
insert into a VALUES ('2022-10-10 10:20:00'::timestamp, 2);
insert into a VALUES ('2022-10-10 10:30:00'::timestamp, 3);
insert into b VALUES ('2022-10-10 10:10:00'::timestamp, 2);
insert into b VALUES ('2022-10-10 10:20:00'::timestamp, 2);
insert into b VALUES ('2022-10-10 10:30:00'::timestamp, 3);

select a.t, a.v * b.v
FROM a join b on a.t=b.t;

┌─────────────────────┬──────────┐
│          t          │ ?column? │
├─────────────────────┼──────────┤
│ 2022-10-10 10:10:00 │        4 │
│ 2022-10-10 10:20:00 │        4 │
│ 2022-10-10 10:30:00 │        9 │
└─────────────────────┴──────────┘

If your data diverge in a few seconds, then you use the time_bucket in the join clause. Example:

select a.t, a.v * b.v
FROM a join b on time_bucket('10 s',a.t)= time_bucket('10s', b.t);

┌─────────────────────┬──────────┐
│          t          │ ?column? │
├─────────────────────┼──────────┤
│ 2022-10-10 10:10:10 │       10 │
│ 2022-10-10 10:20:21 │       18 │
│ 2022-10-10 10:30:01 │       28 │
└─────────────────────┴──────────┘
jonatasdp
  • 1,072
  • 6
  • 8