How to combine separate Time and Date columns as selection criteria?

Grafana Time Series Panel
1

I am not sure how to combine separate Date and Time columns to be the time series?

Table definition

Field, Type, Null, Key, Default, Extra

id_hf_noise_data, int(11), NO, PRI, , auto_increment
Time, time, NO, MUL, ,
Date, date, NO, MUL, ,
samples, int(6), NO, , ,
1Mhz_data, varchar(45), YES, , ,
2Mhz_data, varchar(45), YES, , ,

Data sample
Time,Date,samples,1Mhz_data,2Mhz_data
‘00:02:37’, ‘2018-01-21’, ‘11’, ’ -7.48’, ’ -22.14’
‘00:04:38’, ‘2018-01-21’, ‘12’, ’ -7.65’, ’ -22.39’
‘00:06:37’, ‘2018-01-21’, ‘12’, ’ -7.19’, ’ -22.36’
‘00:08:38’, ‘2018-01-21’, ‘12’, ’ -7.78’, ’ -22.73’
‘00:10:38’, ‘2018-01-21’, ‘12’, ’ -7.52’, ’ -22.67’
‘00:12:37’, ‘2018-01-21’, ‘11’, ’ -7.43’, ’ -22.46’
‘00:14:38’, ‘2018-01-21’, ‘12’, ’ -7.74’, ’ -22.45’

I am not sure how to combine the Date and Time columns for the time series in mysql
This as as far as I got and is only based on date not time combined.

SQL statement
SELECT
MIN(UNIX_TIMESTAMP(‘Time’)) as time_sec,
avg(‘1Mhz_data’) as value
FROM hf_noise_data
WHERE $__timeFilter(‘Date’)

and getting error as the time format is not correct.
Invalid type for column time, must be of type timestamp or unix timestamp, got: <nil> <nil>

2

use the “edit SQL” and combine the date and time with:
STR_TO_DATE(CONCAT(date, ’ ', time), ‘%Y-%m-%d %H:%i:%s’)

example

SELECT
STR_TO_DATE(CONCAT(date, ’ ', time), ‘%Y-%m-%d %H:%i:%s’) AS “time”,
value,
type
FROM table
WHERE
$__timeFilter(time)
ORDER BY time

1 Like
3

Hi, I tried STR_TO_DATE() but it is not working with SQL SERVER.

Among the different things i found this one and it is working great!

(CAST([‘col with date’] as DATETIME) + CAST([‘col with time’ ] as DATETIME)) AS time,

S