mySQL Unixtime issue, any help appreciated

  • What Grafana version and what operating system are you using?

  • What are you trying to achieve?
    Display time based data on a time series graph and create a total based on the grafana time scale selected.

  • How are you trying to achieve it?
    Using the below mySQL table and SQL

  • What happened?
    The graph seems to be shown correctly but any totals are for the whole data set.
    Not the selected i.e. last 7 days etc.

  • What did you expect to happen?
    I have other times series not using a unix time stamp. I was expecting the total to only show for the time scale selected at the top of grafana i.e. 7 days, 2 days etc…

  • Can you copy/paste the configuration(s) that you are having problems with?

interval_start int(10) UNSIGNED NOT NULL,
interval_end int(10) UNSIGNED NOT NULL,
consumption decimal(12,4) NOT NULL,
meter char(128) NOT NULL,
tariff char(128) NOT NULL,
fuel enum(β€˜electricity’,β€˜gas’) NOT NULL,
rate_exc_vat decimal(8,4) DEFAULT NULL,
rate_inc_vat decimal(8,4) DEFAULT NULL,
price_exc_vat decimal(8,4) DEFAULT NULL,
price_inc_vat decimal(8,4) DEFAULT NULL,
reduced_rate_exc_vat decimal(8,4) DEFAULT NULL,
reduced_rate_inc_vat decimal(8,4) DEFAULT NULL,
reduced_price_exc_vat decimal(8,4) DEFAULT NULL,
reduced_price_inc_vat decimal(8,4) DEFAULT NULL

INSERT INTO usage (interval_start, interval_end, consumption, meter, tariff, fuel, rate_exc_vat, rate_inc_vat, price_exc_vat, price_inc_vat, reduced_rate_exc_vat, reduced_rate_inc_vat, reduced_price_exc_vat, reduced_price_inc_vat) VALUES

(1660573800, 1660575600, β€˜0.0660’, β€˜E6S13’, β€˜G-1R-AVRO-VAR-21-09-26-A’, β€˜gas’, β€˜2.7738’, β€˜2.9125’, β€˜0.1831’, β€˜0.1922’, β€˜2.7738’, β€˜2.9125’, β€˜0.1831’, β€˜0.1922’),
(1660575600, 1660577400, β€˜0.0490’, β€˜E6S13’, β€˜G-1R-AVRO-VAR-21-09-26-A’, β€˜gas’, β€˜2.7738’, β€˜2.9125’, β€˜0.1359’, β€˜0.1427’, β€˜2.7738’, β€˜2.9125’, β€˜0.1359’, β€˜0.1427’),
(1660577400, 1660579200, β€˜0.0540’, β€˜E6S13’, β€˜G-1R-AVRO-VAR-21-09-26-A’, β€˜gas’, β€˜2.7738’, β€˜2.9125’, β€˜0.1498’, β€˜0.1573’, β€˜2.7738’, β€˜2.9125’, β€˜0.1498’, β€˜0.1573’),

using sql of
SELECT consumption, FROM_UNIXTIME(interval_end) AS datetime FROM usage WHERE fuel = β€˜gas’
and a transform of
Reduce > reduce fields >calculations > Total

But this gives me a complete table total that does not change with grafana date filtering as my other non unix date panels do.

Any help would be greatly appreciated, Completely out of my depth.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    using the query builder I did get
    db query error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near β€˜usage WHERE interval_end >= 1640882140 AND interval_end <= 1672418140 GROUP BY’ at line 4

  • Did you follow any online instructions? If so, what is the URL?

1 Like