fip
fip

Reputation: 63

Extracting a date selector from a string in postgreSQL and timescaleDB

How do I schedule performant string extraction in postgreSQL and timescaledb?

I want to extract unique dates from a string to use as a selector for a realtime time series database. I use the DISTINCT date as a variable in grafana to limit the records that are displayed. Whenever there is a new date, I extract the date and return DISTINCT values.

A query on the fly does a table scan, which of course is very slow.

The idea of a real-time aggregate seems attractive, but timescale won't permit a DISTINCT clause in the aggregate.

Example data:

|          time          | sensor     | temp |
|----------------------- |------------|------|
| 2024-08-20 12:20:50-07 | A_20240820 |  98  |
| 2024-08-20 12:25:50-07 | B_20240820 | 102  |
| 2024-08-21 12:20:50-07 | A_20240821 | 105  |
| 2024-08-22 12:20:50-07 | A_20240822 | 103  |

Desired result:

| date     |
| -------- |
| 20240820 |
| 20240821 |
| 20240822 |

This does NOT work:

CREATE MATERIALIZED VIEW temp_dates WITH (timescaledb.continuous) AS SELECT DISTINCT RIGHT(sensor,8) AS date FROM temps_idy;
ERROR:  invalid continuous aggregate query
DETAIL:  DISTINCT / DISTINCT ON queries are not supported by continuous aggregates.

Upvotes: 0

Views: 49

Answers (1)

Vérace
Vérace

Reputation: 928

Select 
  right(sensor, 8), 
  count(right(sensor,8)) -- not required, can be removed
From
  Temps_idy
Group by
  Right(sensor, 8)
Order by
  Right(sensor, 8) desc  -- desc optional

Upvotes: 0

Related Questions