I'm new to TimescaleDB and relational databases in general.
I have data in the form
Timestamp | Sensor name 1 | Sensor name2 | ... | Sensor name 300 |
---|---|---|---|---|
yyyy-mm-dd 00:01 | 22.5 | 25 | ... | sensor 300 value |
yyyy-mm-dd 00:02 | ... |
but since I want to store metadata about each sensor (location, type etc) in a second table, I have stored this in TimescaleDB as
timestamp | sensor_id | value |
---|---|---|
yyyy-mm-dd 00:01 | 1 | 22.5 |
yyyy-mm-dd 00:01 | 2 | 25 |
This is fine, although I don't like having the timestamp duplicated 300 times.
But, I need a query that will return data in the original form, one row per unique timestamp, sensor names (from the second table) as columns. I feel like this must be a very normal operation with this kind of data, but I can't for the life of me find out how to do it.
So two questions: is this actually a good way to store data like this in TimescaleDB. From what I can see in the documentation, the raw form of the data is 'wide table' and I could just store it like that, but then I don't know how to link metadata to individual columns. The way I'm storing it now is a 'narrow table', but then I need a way to reconstruct the 'wide' form. Is there a preferred approach in Timescale?
How would I construct a query to get the data in the original 'wide' form? It's essentially a pivot, so would I use crosstab
? Again, this seems like it shouldn't be a strange thing to do, is there a common way that timescale users approach this?