Sample Header Ad - 728x90

TimescaleDb : How to store long time events?

0 votes
0 answers
386 views
I’m trying to use TimescaleDb to store long time events (events started for example 2 days ago and ended 1 hour ago). There are two types of events (current events without end date and historical events with en date). Events (or chunks) should be deleted after one month (based on the event’s end date). My questions are : 1. Is there a way to store events without end date and to keep them in a specific chunk that will never be deleted. Indeed, for current events I don’t want them to be deleted even if the event started more than 1 month ago. 2. When I want to request data, I want to see only events that were active during the selected date range. For example, retrieve all events that were active between '2022/07/24 00:00:00" and “2022/07/24 23:59:59”. By active, I mean event that potentially : - starts before date range and ends after date range - starts before date range and ends between date range - starts between date range and ends after date range - starts between date range and ends between date range. I found the overlaps postgres function but the problem is that Timescale still have to scan all chunks to find matching events. Indeed, as the end date is potentially between “start date range” and “now”, it means the more I want to have an old date range, the more time the request will take. For now, the only solution I found to limit the number of chunks to scan is to add a where clause where end_date > start_date_range. Thanks for your time, hope you will give me some good advice :)
Asked by Anthony Le Gal (1 rep)
Aug 1, 2022, 08:10 AM
Last activity: Aug 1, 2022, 04:06 PM