CODEWITHSUNDEEP
sqldatabasetime-seriestimescaledb
user5648335
user5648335

Reputation: 1

How do I query a new integer value in a timescale DB based on the value of string b?

I have a timescale DB named metric where many different parameters are stored, some are string values and some are integer values. Basically, i have a parameter named "carbon" that is an integer value and I have a string value named "stationName". The goal of this query is to show the carbon value during a given time period ONLY when the stationName is equivalent to "ST2". Any help would on how to accomplish this would be greatly appreciated as this is my first time dealing with a time-series queries.

Below I was add the sample query I have trying so far and the schema of the DB.

DB Schema

timestamp - timestamp value

parameter - Parameter name

data_text - Used when parameter is type of text

data_float - Used when parameter is numeric

    WITH
 -- a and b are for reading data 

a AS ( SELECT 
    timestamp AS t, 
    data_float AS carbon 
  FROM 
    metric 
  WHERE 
    parameter = 'db.C' 
    AND timestamp BETWEEN $START_TIME::timestamp AND $END_TIME
),
 
b AS (
  SELECT
    timestamp AS t,
    data_text AS station
  FROM metric
  WHERE parameter = 'db.StationName'
  AND timestamp BETWEEN $START_TIME::timestamp AND $END_TIME
),
 
c AS (
  SELECT t, carbon, NULL AS station FROM a 
  UNION ALL
  SELECT t, NULL AS carbon, station FROM b
),
 
d AS (
  SELECT *,
         COUNT(carbon) OVER (ORDER BY t) AS grp
  FROM c
),
e AS (
  SELECT t, 
         carbon AS carbon_val, 
         station AS station_val
  FROM d
)
 
SELECT 
  t AS timestamp, 
  carbon_val,
  station_val,
  'db.C_for_ST2' As columns
FROM e
WHERE 
  station_val = "ST2"
  AND t BETWEEN $START_TIME AND $END_TIME```

Upvotes: 0

Views: 70

Answers (1)

jonatasdp
jonatasdp

Reputation: 1412

I'd start breaking down them into views to make the problem easier to solve:

create view station_events as
 SELECT
    timestamp AS t,
    data_text AS station
FROM metric
  WHERE parameter = 'db.StationName'

same for carbon events:

create view carbon_events as SELECT 
    timestamp AS t, 
    data_float AS carbon 
  FROM 
    metric;

Now, a CTE to filter the station:

WITH the_station AS
(select * from station_events where station = 'ST2' and BETWEEN $START_TIME::timestamp AND $END_TIME)
SELECT station, (select count(*) FROM carbon_events WHERE carbon_events.time BETWEEN the_station.time and $END_TIME)) as carbon_count from the_station;

I'm not sure if the query is right, I'm inspired on this topic to answer last part: PostgreSQL: Joining the count of another table

You can also think about maybe using more advanced components like state tracking too.

Upvotes: 0

Related Questions