mySQL Unixtime issue, any help appreciated

Grafana MySQL
1

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

  • 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?

CREATE TABLE usage (
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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?
    no

1 Like