timescale Timeseries example

gistfile1.txt

datetime = Timx.now()
data = [%{a: 0, cluster: 0, time: datetime},
%{a: 1, cluster: 0, time: Timex.shift(datetime, minutes: 3)},
%{a: 2, cluster: 0, time: Timex.shift(datetime, minutes: 6)},
%{a: 3, cluster: 0, time: Timex.shift(datetime, minutes: 9)},
%{a: 4, cluster: 1, time: Timex.shift(datetime, minutes: 12)},
%{a: 5, cluster: 1, time: Timex.shift(datetime, minutes: 15)},
%{a: 6, cluster: 1, time: Timex.shift(datetime, minutes: 18)},
%{a: 7, cluster: 1, time: Timex.shift(datetime, minutes: 21)},
%{a: 8, cluster: 2, time: Timex.shift(datetime, minutes: 23)},
%{a: 9, cluster: 2, time: Timex.shift(datetime, minutes: 26)},
%{a: 10, cluster: 2, time: Timex.shift(datetime, minutes: 29)},
%{a: 11, cluster: 2, time: Timex.shift(datetime, minutes: 32)},
%{a: 12, cluster: 3, time: Timex.shift(datetime, minutes: 35)},
%{a: 13, cluster: 3, time: Timex.shift(datetime, minutes: 38)}]


Extracting a range from a map series based on map elements

I have  a list of maps/structs. I want to group the each list into continous subsets


# I want to find out when the cluster is not the previous value

import Ecto.Query, warn: false
  alias ElixirFiddle.Repo
  alias ElixirFiddle.Composition.Fragment
  alias ElixirFiddle.Composition.FragmentVersion

  Repo.all(Fragment)



  cc -fPIC -I/usr/local/lib/erlang/erts-9.2/include \
     -dynamiclib -undefined dynamic_lookup \
     -o main.so main.c


     g++ -O3 -fpic  -I/usr/local/lib/erlang/erts-9.2/include  -shared -o main.so main.cpp
     g++ -O3 -fpic  -I/usr/local/lib/erlang/erts-9.2/include  -shared -dynamiclib -undefined dynamic_lookup  -o main.so main.cpp

SQL SAmple Data insert

Sample Data SQL Insert 

CREATE TABLE time_series (
  id SERIAL UNIQUE, 
  name TEXT,
  timestamp TIMESTAMPTZ
);

INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:18:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:22:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:25:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:31:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:38:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:44:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:47:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:48:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:49:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:51:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:57:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:59:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:01:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:02:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:08:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:15:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:22:00');

Working Query:

WITH my_series AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) AS rn
    FROM time_series 
)
SELECT o1.id AS id1, o1.timestamp AS date1, o2.id AS id2, o2.timestamp  AS date2, ABS(EXTRACT(EPOCH FROM (o1.timestamp - o2.timestamp))) AS diff
 
  FROM my_series as o1 JOIN my_series  as o2
    ON o1.rn + 1 = o2.rn
  WHERE ABS(EXTRACT(EPOCH FROM (o1.timestamp - o2.timestamp))) < 300;