How to convert a date (string format) into a timestamp (for primary key)?

Telegraf
,
1

Hello,

I’m having trouble transforming a string date into a timestamp to use as a primary key.
.
Here is an example of a message I am processing:

{"applicationID":"1","applicationName":"chirp-app","deviceName":"0200000002","devEUI":"60c5a8fffe76ea87","rxInfo": [{"gatewayID":"60c5a8fffe76154b","uplinkID":"e97b990f-1cf0-427f-b49e-69a0d8804f81","name":"rak7249","time":"2020-10-26T15:21:38. 911653Z","rssi":-87,"loRaSNR":6.5,"location":{"latitude":43. 504,"longitude":1.53007,"altitude":324}}],"txInfo":{"frequency":867100000,"dr":0},"adr":false,"fCnt":1723,"fPort":8,"data":"CAIBZQdojwZzJu4CZwCMBAILog==","object":{"analogInput":{"4":29. 78,"8":3.57},"barometer":{"6":996.6},"humiditySensor":{"7":71.5},"temperatureSensor":{"2":14}}}

.
The format of my string: 2020-10-26T15:20:42.06126Z.
.
The part of my telegraf.conf that transforms it into time :

[[processors.rename]]
  order = 6
  [[processors.rename.replace]]
    tag = "rxInfo_0_time"
    dest = "time"

How to use a date like 2020-10-26T15:20:42.06126Z instead of the timestamp of the data insertion in influxdb?

Thanks to all

2

afaik you can’t do that, the time column can’t be specified or overridden in general unless the input you use has some settings for it.

Since you are parsing JSON data, I’d like to know which is the input plugin, because if it allows you to choose the data format (JSON in this case) then you should be able to achieve that as the Telegraf Json parser allows you to specify where to take the timestamp in its settings json_time_key, json_time_format, json_timezone

3

Thanks for your answer Giovanni :slight_smile:

Here is my input plugin :

[[inputs.mqtt_consumer]]
  servers     = ["ssl://xxxxxxxxxxxxxx:8883"]
  topics      = ["application/1/device/+/event/up"]
  qos         = 0
  username    = "user"
  password    = "password"
  data_format = "json"
  tls_cert    = "/etc/ssl/ca-cert.crt"
  tls_key     = "/etc/ssl/ca-cert.key"
  tag_keys    = [
    "deviceName",
    "devEUI",
    "rxInfo_0_location_altitude",
    "rxInfo_0_location_latitude",
    "rxInfo_0_location_longitude",
  ]
  fieldpass    = [
    "object_barometer_6",
    "object_humiditySensor_7",
    "object_temperatureSensor_2",
  ]

Here is my output plugin :

[[outputs.influxdb]]
  urls                   = ["https://xxxxxxxxxxxxxxxxxxxxxx:8086"]
  database               = "database"
  skip_database_creation = true
  username               = "user"
  password               = "password"
  namepass               = ["device_*"]
  namedrop               = ["device_"]
  tls_cert               = "/etc/ssl/ca-cert.crt"
  tls_key                = "/etc/ssl/ca-cert.key"
  fielddrop              = ["uplink_message_settings_timestamp"]
4

Something like this should work

{...}
json_time_key = "rxInfo_0_time"
json_time_format = "___" 
json_timezone = "UTC"

I’m not sure about the time format, the string looks ok and a simple “2020-10-26T15:20:42.06126” might work, the examples limit themselves at seconds precision tho, so I’m not sure about the milliseconds, just have a look at what can be passed (you will find all in the telegraf and GO docs, start from quote above)

1 Like
5

Hello Giovanni
.
Thank you for your precious help. It works now, I have the timestamp of the gateway that goes back in the data key. Thanks again !!
.
Here is the exact configuration of the plugin [[inputs.mqtt_consumer]], it will help those who ask this question:

[[inputs.mqtt_consumer]]
  servers = ["ssl://xxxxxxxxxxxxxx:8883"]
  topics = ["application/1/device/+/event/up"]]
  qos = 0
  username = "user
  password = "password
  data_format = "json
  tls_cert = "/etc/ssl/ca-cert.crt
  tls_key = "/etc/ssl/ca-cert.key".
  tag_keys = [
    "deviceName",
    "devEUI",
    "rxInfo_0_location_altitude",
    "rxInfo_0_location_latitude",
    "rxInfo_0_location_longitude",
  ]
  fieldpass = [
    "object_barometer_6",
    "object_humiditySensor_7",
    "object_temperatureSensor_2",
  ]
  # parse time
  json_time_format = "2006-01-02T15:04:05.0000Z" 
  json_time_key = "rxInfo_0_time". 
  json_timezone = "UTC
2 Likes