Setting a fixed time interval for a panel

[mortensickel]

I am using grafana 10.3 installed on my own debian system, fetching data from a postgreSQL-database.

I have a dashboard giving me an overview of the power consumption in my home, including what is produced by solar panels. I have made a time series panel where I can compare the so far accumulated production over the current day with production for the last week:

For this panel, I have set the query options to +36h relative time and 12h time shift. Then I will be able to see the graphs for most of the day, but it is sliding leftwards as the day goes on and I am not using the “screen real estate” in a good way, as I need to have buffers on both sides.

Is it somehow possible to set a panel to always show the data from 00:00 to 23:59 (or any other time interval) for the current day?

(To get the previous days data moved till today, I use “timefield::time+now()::date time” as the timestamp)

[sowdenraymond]

you can set the query time options here:

[mortensickel]

That was what I have been using, so I can only set the time relative to present time. I hoped I could somehow define a fixed x-axis defined from today’s date or to just zoom in on the time perion in which there are data.

[sowdenraymond]

Example	Relative time field
Last 5 minutes	now-5m
The day so far	now/d
Last 5 days	now-5d/d
This week so far	now/w
Last 2 years	now-2y/y

[raghavasmail]

You got solution for this?

[mortensickel]

I ended up making a plotly panel. I’ll post the details later

[mortensickel]

I used the plotly panel plugin, the database table has two fields that are being used here: time, the timestamp for the measurement. ppv_total the watts being produced at that time.
I started off with the same query as for the time series panel:

select to_char(time::date,'YYYY-mm-dd') as parameter, 
now()::date+time::time as time,   
round(sum(ppv_total/12) over (partition by time::date order by time)) as " " 
from pv where pv.time > now()::date - interval '7 days'  
order by time;

“select to_char(time::date,‘YYYY-mm-dd’)” gives me the date of the measurement as text, “now()::date+time::time” moves the measurement to today so I get it all on the same scale. “round(sum(ppv_total/12) over (partition by time::date order by time))” gives the sum produced up to a given time of day. /12 is because I get a number of watt each five minutes and I want to get it to kWh,

So the query will give me a table like

So far so good. For the plotly panel, a bit of coding is needed. For “Data of the plot”, I have

[
  {
    "line": {
      "color": "rgba(255,255,255,255)",
      "width": 1
    }
  }
]

I think this is overridden later on. For Layout, I have quite a good chunk of json:

{
  "annotations": [
    {
      "showarrow": false,
      "text": "kWh",
      "textangle": -90,
      "x": -0.03,
      "xanchor": "right",
      "xref": "paper",
      "y": 0.5,
      "yanchor": "right",
      "yref": "paper"
    }
  ],
  "colorway": "D3",
  "font": {
    "color": "darkgrey",
    "family": "Inter",
    "weight": 400
  },
  "hovermode": "closest",
  "legend": {
    "entrywidth": 0,
    "itemsizing": "constant",
    "itemwidth": 8,
    "orientation": "h",
    "x": 0,
    "xanchor": "left",
    "y": -0.5,
    "yanchor": "middle"
  },
  "margin": {
    "b": 10,
    "l": 40,
    "r": 10,
    "t": 15
  },
  "paper_bgcolor": "rgba(0,0,0,0)",
  "plot_bgcolor": "rgba(0,0,0,0)",
  "xaxis": {
    "autorange": true,
    "color": "#444",
    "gridcolor": "#444",
    "gridwidth": 1,
    "range": [
      "2024-06-10 07:20:01",
      "2024-06-11 00:15:01"
    ],
    "tickformat": "%H:%M",
    "type": "date"
  },
  "yaxis": {
    "autorange": true,
    "color": "#444",
    "gridcolor": "#344",
    "range": [
      -0.8240555555555558,
      15.677055555555556
    ],
    "type": "linear"
  }
}

“Configuration” is empty, and then I have some javascript to go from the grafana table to what I want in plotly, “Script”:

let dataset = []
let colors = [0,"#2E91E5", "#E15F99", "#1CA71C", "#FB0D0D", "#DA16FF",
                                    "#B68100", "#750D86", "#222A2A","#EB663B"];
for (set =1; set<9; set++){
  let x = [];
  let y = [];
  try{
    data.series[0].fields[set].values.forEach(function (item, index) {
        if (item > ''){
            y.push(item/1000);
            x.push(data.series[0].fields[0].values[index]);
        }
    }); 
    if (set == 8){
        line={width:4,
        color: "yellow"};
    }else{
        line={width:2,
        color: colors[set]};
    }
    let serie = {
    x : x,
    y : y,
    name : data.series[0].fields[set].labels.parameter.slice(-5),
    line: line
    };
    dataset.push(serie)
  }catch(e){
     // missing data
  }
}
return {
  data : dataset,
  config : {
    displayModeBar: false
  }
}

And in the end “Click Script” is just

window.updateVariables({query:{'var-project':'test'}, partial: true})

It was quite a bit to pull together, but now it is working beautifully, although it does not look like a real grafana graph panel, but not too bad.