TimeFilter combined from Date and Time

Grafana
1

I have a DB and there is no DATETIME datatype only a DATE and TIME as separate column.

Now I tried to combine those two with the mysql TIMESTAMP function like:

TIMESTAMP(opDate, opTime)

resulting in this querry which does not work:

SELECT
TIMESTAMP(opDate, opTime) AS “time”,
Temperature,
Humidity,
WindDirection,
WindVelocity
FROM daten
WHERE
time BETWEEN $__timeFrom(TIMESTAMP(opDate, opTime)) and $__timeTo(TIMESTAMP(opDate, opTime))
ORDER BY TIMESTAMP(opDate, opTime)

any hint or idea how to fix it?

Andy

2

That is a good question.

iam also interested in a solution.

3

Anyone ever find a solution to this?

4

Yes.

SELECT 
    CAST(CONCAT(CAST(date AS varchar), ' ', time) AS timestamp) AS date_time,
    $__column
FROM 
    $__table
WHERE 
    $__timeFilter(CAST(CONCAT(CAST(date AS varchar), ' ', time) AS timestamp))
ORDER BY date_time ASC
LIMIT 1000

From the inside out, you cast the date as a string, concatenate it with the time separated by a space, cast that as a timestamp, and use the grafana timeFilter macro on that.

You can use just the timeFilter line in any queries, SELECT it is not necessary, unless you want to order in a different way than the default on it; note that you can’t put date_time into the timeFilter for some reason, even though you named it; it will throw an error as of this writing, so you have to repeat the large CAST statement.

5

you will pay in performance for that x4 CAST and CONCAT