How to show Histogram or Timeseries Data in graph for a table with no Timestamp

Hello All,

I am new to Grafana and was trying to evaluate a banking requirement. Below is the Scenario.

  • The DB Can be Mysql or Oracle. Table name is TransactionMaster and The column is the transaction number an ID, Type and Status.

  • Status will be E = error, P= Processed and U = Unprocessed.

  • So if the jobs are running the Processed Count (i.e total processed transactions ) will be increasing and unprocessed may or may not depending on the incoming load/injection.

Now there is no timestamp about the last updated status.

How can I build a graph/histogram/timeseries to show in last 1 minute system was able to process the transaction as the processed/Error graph has gone up.

One way is to create one table and have a job running that gets the stats from TransactionMaster and update in this table with timestamp about how many transactions were processed/updated now. And then have a timeseries graph to poll on this new table rather than existing TransactionMaster table.

But is there any other way in which Grafana tracks the data fetched previously and display it like we can supply come DB functions like SysDate e.g

select transactionType, Status, SysDate ,Count(1)
from TransactionMaster
group by transactionType, Status

When this query is executed every 1 min, Grafana should display the value for every minute what this query resulted one hour back etc. Using this I can track if the processed/error graph is moving or there is an issue in the jobs

Note: SysDate is not a coulmn it is just a function that give you current timestamp.

I hope I was able to explain the question