Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
timescale Timeseries example
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
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.