How to remove old events from Thingsboard?

Question

What should I do to properly remove 'event' entries from Thingsboard?

As far as I know, the current API does not provide a way to remove events. It seems like the only way is to directly delete the records in DB.

By the way, I'm using PostgreSQL as DB.

Triviality

Answer 1

Your assumption is correct. You will need to execute a SQL script to cleanup the "events" table. I must note that for Cassandra DB we already have "cassandra.query. ts_key_value_ttl" and "cassandra.query.events_ttl" configuration parameter to automate this process.

Miltonmilty

Thanks for your response. Too bad we are not using Cassandra. Do you know how to tell the created time of an event from the uuid? – Triviality

I also use PostgreSQL and I need to cleanup the events table by deleting old records on it. So I have the same question as @Triviality . – Maya

We can only store timeseries data in Cassandra, don't know how to store event data in Cassandra. – Gilstrap

Answer 2

After two hours of research in Thingsboard source code, I found the solution.

The date is contained in the uid_event field in V1 UUID format.

So first, you need to write a function uuid_timestampin order to convert the UUID to a timestamp. I found the solution, here: https://mcmap.net/q/773581/-cast-or-extract-timestamp-from-v1-uuid-in-postgresql

CREATE FUNCTION uuid_timestamp(id uuid) RETURNS timestamptz AS $$
  select TIMESTAMP WITH TIME ZONE 'epoch' +
      (((('x' || lpad(split_part(id::text, '-', 1), 16, '0'))::bit(64)::bigint) +
      (('x' || lpad(split_part(id::text, '-', 2), 16, '0'))::bit(64)::bigint << 32) +
      ((('x' || lpad(split_part(id::text, '-', 3), 16, '0'))::bit(64)::bigint&4095) << 48) - 122192928000000000) / 10000000 ) * INTERVAL '1 second';    
$$ LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;

After that, to delete all events older than 30 days ago, you can run a query like:

DELETE FROM public.event WHERE uuid_timestamp(event_uid::uuid) < now() - '30 days'::interval;

Maya