Can't get data from MS SQL graphed

Grafana MSSQL
1

Hi,

Quite new to Grafana, so my question may seem trivial.

I’m trying to graph some data from a MS SQL. The data source is set up, and functioning.

However, I can’t get data graphed. I’m trying to fetch the data with the following query:

SELECT
LOGGTIME as time,
MEASVALUE01
FROM
TREND_CCOND
WHERE
$__timeFilter(LOGGTIME)
ORDER BY 1

image
image1470×829 41.8 KB

This is the data from the database, just to show that some data are available.

image

Any idea what I’m doing wrong?

BR O

2

what format is LOGGTIME in?

3

Hi Torkel,

The LOGGTIME format is INT

image

BR O

4

@ob75 try and multiply loggtime with 10 to get seconds precision given LOGGTIME holds values in epoch format (seconds elapsed since 1970)

5

Should I be able to achieve that by:

SELECT
LOGGTIME * ‘10’ as time,
MEASVALUE01
FROM
TREND_CCOND
WHERE
$__timeFilter(LOGGTIME)
ORDER BY 1

6

Either

SELECT
LOGGTIME * 10 as time,
MEASVALUE01
FROM
TREND_CCOND
WHERE
$__unixEpochFilter(LOGGTIME*10)
ORDER BY 1

or if that don’t work

SELECT
LOGGTIME * 10 as time,
MEASVALUE01
FROM
TREND_CCOND
WHERE
LOGGTIME*10 >= $__unixEpochFrom() AND LOGGTIME*10 <= $__unixEpochTo()
ORDER BY 1

Performance wise it would be better if you had seconds precision stored in your table.

7

Still no graph. :frowning:

I will make a calculated database column, based on LOGGTIME * 10. What format should be the correct one to choose?

8

I think your issue is that your timestamps not are epoch (seconds since 1970) or are they? Checking 314753895 on https://www.epochconverter.com/ results in Saturday 22 December 1979 23:38:15 - is that expected?

9

Sorry, I’m pretty sure you are right about that, stupid of me to assume it was.

The table contains more columns, and the value from LOGGTIME consistently don’t match the timestamp in LOGGTIMECHAR

image

So I guess I have to find a way, to convert LOGGTIMECHAR to epoch, right?

10

I have no idea. If LOGGTIME and LOGGTIMECHAR is the same thing, but different representation I suppose the LOGGTIME is the number of something since some starting point in time. If you know the starting point in time and something you may be able to convert it to proper epoch format.

Epoch, some starting point=1st Jan 1970, something=seconds.

Otherwise your only option is to try and parse the LOGGTIMECHAR. If you have possibility to change the table schema and how you write the data that would probably be the optimal solution for being able to use it in a proper and supported way in Grafana.

Good luck!

1 Like
11

Want to share the solution on this one.

As mentioned by @mefraimsson my problem was, that the database column I expected to be EPOCH time wasn’t EPOCH time.

So I started to figure out a way to parse another column (LOGTIMECHAR) containing a human readable timestamp as EPOCH.

It turned out to be quite effortless. I added a column named EPOCH to the tabel schema, containing the following computed string:

(datediff(second,‘1970-01-01 00:00:00’,[LOGGTIMECHAR]))

That did the trick, and it’s now possible to graph the data.

Thank you for contribution!

Best regards Ole.