Cannot use DATE_FORMAT inside SQL queries DBSource: MySQL

Grafana
1

Hello,
I’ve created dashboard with different time intervals (hour, day, week, month), template variable $period should change it.
I’ve detected that I cannot use DATE_FORMAT(date,“%Y-%m-01 00:00:00”) inside the query. I must apply FROM_UNIXTIME UNIX_TIMESTAMP functions. Could you fix it?

My Original Query is:
SELECT
UNIX_TIMESTAMP(CASE WHEN $period=“day” THEN order_date WHEN $period=“week” THEN DATE_ADD(order_date, INTERVAL(-WEEKDAY(order_date)) DAY) WHEN $period=“month” THEN FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(order_date,“%Y-%m-01 00:00:00”))) ELSE order_datetime END) as time_sec,
SUM(metric) as value,
tariff_name as metric
FROM tariff_service_order_report
WHERE $__timeFilter(CASE WHEN $period=“day” THEN order_date ELSE order_datetime END) AND source_name IN($src)
GROUP BY 1,3
ORDER BY 1 ASC

Query with error:
SELECT
UNIX_TIMESTAMP(CASE WHEN $period=“day” THEN order_date WHEN $period=“week” THEN DATE_ADD(order_date, INTERVAL(-WEEKDAY(order_date)) DAY) WHEN $period=“month” THEN DATE_FORMAT(order_date,“%Y-%m-01 00:00:00”) ELSE order_datetime END) as time_sec,
SUM(metric_) a_s value,
tariff_name as metric
FROM tariff_service_order_report
WHERE $__timeFilter(CASE WHEN $period=“day” THEN order_date ELSE order_datetime END) AND source_name IN($src)
GROUP BY 1,3
ORDER BY 1 ASC

Error is: “Found row with no time value”

Original.png1847×942 111 KB
with_error.png1843×962 98.9 KB

2

This query seems to return no time value

3

Difference between these queries that I type:
FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(order_date,"%Y-%m-01 00:00:00")))
instead of
DATE_FORMAT(order_date,"%Y-%m-01 00:00:00")
If it is normal then ok!

4

the time_sec column must be a unix epoch in seconds. So it must be the same time that UNIX_TIMESTAMP returns

5

Right.
And I do similar this queries:
SELECT UNIX_TIMESTAMP(CASE WHEN “zzz”=“day” THEN NOW() ELSE DATE_FORMAT(NOW(),"%Y-%m-01 00:00:00") END) AS time_sec;
But it doesn’t work in grafana now. So I should do like this:
SELECT UNIX_TIMESTAMP(CASE WHEN “zzz”=“day” THEN NOW() ELSE FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(NOW(),"%Y-%m-01 00:00:00"))) END) AS time_sec;
Bouth of them time_sec column has unix epoch in seconds.

And it looks like work in grafana when you do like this:
SELECT UNIX_TIMESTAMP(DATE_FORMAT(order_date,"%Y-%m-01 00:00:00")) AS time_sec, …
and doesn’t work when
SELECT UNIX_TIMESTAMP(CASE WHEN $period=“day” THEN DATE_FORMAT(order_date,"%Y-%m-01 00:00:00") ELSE order_date END) AS time_sec, …
and work again:
SELECT UNIX_TIMESTAMP(CASE WHEN $period=“day” THEN FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(order_date,"%Y-%m-01 00:00:00"))) ELSE order_date END) AS time_sec, …