In Postgres, given a collection of date ranges like: meter_id device_id start_at end_at meter1 device1 2020-01-02 10:30 2025-01-02 14:00 meter2 device1 2020-01-02 10:30 2020-01-02 11:30 meter3 device1 2020-01-02 10:30 2020-01-03 11:30 I want to bucket the ranges into: a range…
I would like to average several time-series arrays of different length using Javascript in an efficient manner. The reason for having different length is because some data is missing in some of the arrays. Time should be used as the…
I have a json like this: { "2023-06":7.2, "2023-08":6.7, "2023-07":5, "2022-04":3.5, "2023-05":6.1 } How can I sort it to get it in chronological order? Starting with latest date and ending with the most far in history? I am able to…
I'm seeking advice on how to optimize my timeseries database setup, which should handle a large volume of time-series data. I have around 20,000 time-series profiles with a one-year duration, using a quarterly time resolution (4 timestamps per hour). This…
I have created a table like this CREATE TABLE IF NOT EXISTS public.table_name( id SERIAL PRIMARY KEY, user_id int4 NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW(), ); SELECT create_hypertable('table_name', 'created_at', if_not_exists => TRUE); CREATE INDEX IF NOT EXISTS ix_table_name_user_id…
I'm trying to calculate the time weighted average using LOCF in a 24 hour interval using the following data: Time Value 2020-01-01 00:00:00 1 2020-01-01 23:00:00 1000 The query looks as follows: SELECT time_bucket('1 days', time) AS timebucket, average(time_weight( 'locf',…
I am using DuckDB and want to process some time-series data that has the following format: ┌─────────────────────┬─────────┬─────────┬─────────┬─────────┬────────┬────────────┐ │ Timestamps │ Open │ High │ Low │ Close │ Volume │ CustomDate │ │ timestamp │ double │ double │ double…
I have the following table ticks datetime lowest_tick tick_lower 2022-10-01 00:02:00 204406 204306 2022-10-01 00:03:00 204525 204425 2022-10-01 00:04:00 204414 204314 2022-10-01 00:05:00 204200 204100 2022-10-01 00:06:00 204220 204120 2022-10-01 00:07:00 204120 204020 What I want to get is to…
I have a table tab1 in timescale db which has 3 columns tag, time, value. time and tag make up the pk for the table: (time, tag). There are more than 500 lakhs (50 000 000) of rows. I need…
Trying to write a query from the following table that calculates a count of records for every day date(timestamp) and the prior 4 days. So basically a rolling count of records for every previous 5 days. Every time i do…