Gather and graph table of historical runtimes

[jasonhill-ds]

Apologies for what might be a newbie question, and I might be barking up the wrong tree with trying to do this with pgwatch2, but:
If I have a table in the database to be monitored that has a Timestamp as one column and an Int as another column, is there a way to set up a custom metric to be able to graph the integer by timestamp?

If I write a metric which just does a "select rundate, num_secs from run_table order by rundate" I can get the data I need, but this isn't the sort of "one metric per gather" that pgwatch2 expects. I don't want to store all the results everytime the metric runs... I could just get the latest row from the table and run the check once per day, but that doesn't seems like a hack.

An example of the data that I want to graph below. Graph say second_taken vs start_time.

   start_time      |      end_time       | second_taken
---------------------+---------------------+--------------
2024-03-24 07:00:00 | 2024-03-24 09:28:17 |         8897
2024-03-23 07:00:00 | 2024-03-23 09:35:09 |         9309
2024-03-22 07:00:00 | 2024-03-22 11:50:31 |        17431
2024-03-21 07:00:00 | 2024-03-21 09:28:46 |         8926
2024-03-20 07:00:00 | 2024-03-20 09:32:56 |         9176
2024-03-19 07:00:00 | 2024-03-19 09:29:59 |         8999

Thanks and apologies for such a rudimentary question.
J

[pashagolub]

If I have a table in the database to be monitored that has a Timestamp as one column and an Int as another column, is there a way to set up a custom metric to be able to graph the integer by timestamp?

Yes, absolutely.

If I write a metric which just does a "select rundate, num_secs from run_table order by rundate" I can get the data I need, but this isn't the sort of "one metric per gather" that pgwatch2 expects. I don't want to store all the results everytime the metric runs... I could just get the latest row from the table and run the check once per day, but that doesn't seems like a hack.

Sorry, I don't get it.

The easiest way would be to take an existing metric and apply some changes to it.

[jasonhill-ds]

My understanding of how pgwatch2 gathers and stores metrics is that the collector periodically connects to the target database, runs some SQL that is meant to return a metric in a format like "epoch_ns, value1, value2... valueN". This is then stored in a metric table in the pgwatch2 storage (postgres, InfluxDB etc).
The issue that imagine is that I don't want to store this data on every scrape. The table in the DB to be monitored has the metrics that I want to graph already stored as per the example above. I don't want to store that data repeatedly with different epoch_ns for each scrape in the metrics table in the pgwatch2 datastore.

I guess that the option would be to simply use the PostgreSQL datasource rather than pg-metrics and just query the table directly and graph the data returned.