How to convert clickhouse date understandable to Grafana graph?

Question

My Query is below:

SELECT
  (date),
  CVC_Demand_Per_Subscriber
FROM
  (SELECT
    date,
    sum(Max_Utilization) as SUM_Max_Util,
    sum(AVC) as SUM_Total_Active_AVCs,
    (SUM_Max_Util/SUM_Total_Active_AVCs) as CVC_Demand_Per_Subscriber
  FROM
  (
        SELECT
            date,
            cvc as CVC,
            avc as AVC,
            bandwidth,
            round((max(lout)/1000000),2) as Max_Utilization,
            ((((max(lout) / bandwidth) * 100)) / 1000000) as Max_Utilization_Percent,
            (Max_Utilization/AVC) as CVC_Demand_Per_Subscriber
        FROM
        (
            SELECT
                date,
                path[2] as cvc,
                bandwidth,
                avc,
                max(load_out) as lout
            FROM noc.interface    
        ANY INNER JOIN
        (
                SELECT
                    cvcid as cvc,
                    bandwidth,
                    activeavc as avc
                FROM dictionaries.nsi_cvcs
                GROUP BY
                    cvc,
                    avc,
                    bandwidth
        ) USING  cvc
            WHERE
                managed_object IN (
            SELECT bi_id
            FROM dictionaries.managedobject
            WHERE nbn = 1)
                AND(date >= today()- 7)
            GROUP BY
                date,
                cvc,
                avc,
                bandwidth
            ORDER BY
                date,
                cvc,
                avc
        )
        GROUP BY
            date,
            cvc,
            avc,
            bandwidth
  ) 
  GROUP BY date
  ORDER BY date ASC)
  tmp 

I am getting the result data when i select Table in Grafana like below:

Time                CVC_Demand_Per_Subscriber
2021-07-19 00:00:00 1.61
2021-07-18 00:00:00 2.70
2021-07-17 00:00:00 2.90
2021-07-16 00:00:00 2.83
2021-07-15 00:00:00 2.54
2021-07-14 00:00:00 2.38
2021-07-13 00:00:00 2.39
2021-07-12 00:00:00 0.64

But when i change it to Graph, i dont see the graph plotted with the values, according to the dates. It does not say "no data" but an empty graph.

Please Help me where i am wrong?

I tried the below but no luck:

1. Converted the date with UNIX_TIMESTAMP
2. to_char(date_format)
3. $__timeGroup()
4. $__time

Please also suggest optimization on the query.

Answer 1

It needs to:

• define Column:DateTime as Time

• set the sql-query

SELECT
    $timeSeries as t,
    sum(CVC_Demand_Per_Subscriber) value
FROM  (
  /* emulate the test dataset */
  SELECT toDateTime(data.1) AS Time, data.2 AS CVC_Demand_Per_Subscriber
  FROM (
    SELECT arrayJoin([
      ('2021-07-19 00:00:00', 1.61),
      ('2021-07-18 00:00:00', 2.70),
      ('2021-07-17 00:00:00', 2.90),
      ('2021-07-16 00:00:00', 2.83),
      ('2021-07-15 00:00:00', 2.54),
      ('2021-07-14 00:00:00', 2.38),
      ('2021-07-13 00:00:00', 2.39),
      ('2021-07-12 00:00:00', 0.64)]) as data)
  )
WHERE $timeFilter
GROUP BY t
ORDER BY t

---

When the graph is empty (displayed 'No data') and no query error need to check ClickHouse Datasource settigs to make sure that Add CORS flag to requests is enabled: