Problem with mapping SQL timestamp with InfluxDB time in Telegraf

Telegraf
,
1

Hi,

I am trying to migrate data from MSSQL to InfluxDB for testing purpose.

I have set up entire telegraf config but still have a problem with mapping “time_event” field from MSSQL to timestamp in InfluxDB. Format from MSSQL is datetime2(7). I tried to convert it into different format, without success. It still shows time_event as field and timestamp is created by InfluxDB.
Same problem I seem to have with creating tag. “machine_id” field defined as a tag still shows up in test logs as a field.

Below is my config for SQL input. Most likely it is something basic that I am missing, but will be super grateful for support.

[[inputs.sql.query]]
query = “SELECT time_event, machine_id, recipe, machine_state, machine_mode, machine_speed, machine_speed_t, good_count, reject_count FROM dbo.oee1 WHERE time_event > DATEADD(second, -120, GETUTCDATE())”

measurement = “production_oee”
time_column = “time_event”
tag_columns_include = [“machine_id”]
field_columns_int = [“machine_state”, “machine_mode”, “machine_speed_t”, “good_count”, “reject_count”]
field_columns_float = [“machine_speed”]
field_columns_string = [“recipe”]

[[processors.converter]]
[processors.converter.tags]
string = [“machine_id”]

[[processors.dedup]]
dedup_interval = “360s”

Logs from testing Telegraf. machine_id as well as time_event are still in the fields.
production_oee,host=xxx good_count=7499786i,machine_id=“1021”,machine_mode=1i,machine_speed=0,machine_speed_t=24i,machine_state=9i,recipe=“xx”,reject_count=120217i,time_event=1772537862974086100i 1772537863000000000

2

@Michal1211 You may need to cast your time_event column to a Unix timestamp. Something similar to:

DECLARE @UnixEpoch DATETIME2(7) = '1970-01-01 00:00:00.0000000';

SELECT DATEDIFF_BIG(SECOND, @UnixEpoch, "time_event") as "time_event_unix"

And then specify the timestamp format in your Telegraf config:

[[inputs.sql.query]]
# ...
time_column = “time_event_unix”
time_format = "unix"
# ...
3

Hi Michal,

It looks like Telegraf isn’t properly interpreting time_event as the timestamp column. With MSSQL datetime2(7), make sure the value is returned in a format Telegraf can parse correctly (RFC3339 is usually safest). You could try explicitly converting it in your query, for example:

SELECT 
  CONVERT(varchar, time_event, 127) AS time_event,
  machine_id, recipe, machine_state, machine_mode, 
  machine_speed, machine_speed_t, good_count, reject_count
FROM dbo.oee1
WHERE time_event > DATEADD(second, -120, GETUTCDATE())

Also double-check that time_column = "time_event" is correctly aligned with the returned column format.

For machine_id, since you’re already using tag_columns_include, you typically don’t need the converter processor unless there’s a type mismatch. Make sure MSSQL is returning it as a string (or cast it explicitly).

I found a similar configuration explanation discussed on this website, which helped clarify how Telegraf handles time parsing and tag mapping.

Hope this helps you narrow it down