7

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.

skangmy
  • 117
  • 1
  • 3
  • 10

2 Answers2

6

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://stackoverflow.com/a/24191574/5300212 

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;
David Dorchies
  • 336
  • 2
  • 11
0

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.

Andrew
  • 722
  • 5
  • 13
  • 1
    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? – skangmy Feb 12 '19 at 07:01
  • 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 @skangmy . – David Dorchies Sep 02 '19 at 09:40
  • We can only store timeseries data in Cassandra, don't know how to store event data in Cassandra. – Makson Lee Jul 19 '22 at 00:50