Update all time index in a TimescaleDB/PostgreSQL hypertable?

Question

I am using an open-source time-series database named TimescaleDB ( based on PostgreSQL ).

Assuming this table :

CREATE TABLE order (
  time                  TIMESTAMPTZ NOT NULL,
  product               text,
  price                 DOUBLE PRECISION,
  qty                   DOUBLE PRECISION 
);

Next, I transform it into a hypertable with :

SELECT create_hypertable('order', 'time');

Next, insert some data (more than 5 millions rows) :

2020-01-01T12:23:52.1235,product1,10,1
2020-01-01T12:23:53.5496,product2,52,7
2020-01-01T12:23:55.3512,product1,23,5
[...]

I need then to update data to get a time index minus 1h interval, like this :

2020-01-01T11:23:52.1235,product1,10,1
2020-01-01T11:23:53.5496,product2,52,7
2020-01-01T11:23:55.3512,product1,23,5
[...]

What is the most efficient method (duration) to alter the time index in this hypertable in order to remove a 1h interval on all data inside the order table ?

do you always delete data that is older than one hour or do you only want to delete that is older than 1 hour after a new data import? — steve, Feb 03 '20 at 00:11
I would to alter the index for the actual data only... the futures new data (which will be inserted after the correction) will have a good time index. — A. STEFANI, Feb 04 '20 at 14:29

score 0 · Answer 1 · answered Feb 04 '20 at 06:51

not sure if partitioning is available in Timescale, that would ease the process by putting partitions based on the time-range or even date-range.

See if this is one of the options available that way you can just drop the partition based off of a range and voila!

Update all time index in a TimescaleDB/PostgreSQL hypertable?

1 Answers1