Mysql varchar date and timeseries

1

Hi,

I am new to Grafana. I have my table in the following format:
Column Datatype
Order date. varchar
Status. varchar
Record. int
Order date is in the format : 2020-01-23 09:31:29.709

I tried to convert varchar to date. This query shows no errors and no data.
SELECT
UNIX_TIMESTAMP(STR_TO_DATE(order date,"%Y-%m-%d %H:%i:%s")) as order_date,
record as value,
status as metric
FROM Table
WHERE $__timeFilter(UNIX_TIMESTAMP(STR_TO_DATE(order date,"%Y-%m-%d %H:%i:%s"))
ORDER BY order_date ASC;

Thanks in Advance!

2

Hi,

The macros dont allow calling funtions inside. So you need change it…

$__timeFilter(UNIX_TIMESTAMP(STR_TO_DATE(order date,"%Y-%m-%d %H:%i:%s"))

to

UNIX_TIMESTAMP(STR_TO_DATE(order date,"%Y-%m-%d %H:%i:%s") BETWEEN $__timeFrom() AND $__timeTo()
3

I changed it to:

select UNIX_TIMESTAMP(STR_TO_DATE(order_date,"%Y-%m-%d")) as time_sec,
sum(record) as value,
record as metric
FROM Table
where UNIX_TIMESTAMP(STR_TO_DATE(order_date,"%Y-%m-%d") BETWEEN __timeFrom() AND __timeTo()

Still shows no data points.