Timestamp_format if timestamp value is iso8601 but wrapped in quotes?

Hi folks,

I have json data that is fairly straight forward but sadly, the time value is in quotes :frowning:

{
    "data": [
        {
            "time": "2023-05-04T23:10:00",
            "value": 1000
        },
    ]
}

So if I just set timestamp_format = “2023-05-04T23:10:00” (http.json_v2), nothing returns (works just fine if I don’t specify a format, but with an extra default timestamp I don’t want of course). I’ve been looking for a way to escape or wildcard the quotes but I guess my duckduckgo-fu is weak …

Is there something I can do here that doesn’t involve me downloading the data first, cleaning it up in python and then pushing it …

Thanks in advance!

Valentin

Hello @Valentiiiin,
Hmm wait I’m confused. What is the extra default timestamp you don’t want of course?
Are you specifying the timestamp column?

timestamp_path = “” # A string with valid GJSON path syntax to a valid timestamp (single value)
You gotta use gjson syntax
gjson/SYNTAX.md at v1.7.5 · tidwall/gjson · GitHub
So yours would be
data.1
I do believe.

Here are a collection of examples that can be helpful too:

You might have to go the python route but you could do it with telegraf using the execd processor plugin:

here’s an example repo that uses it so you can understand the file structure

Hi @Anaisdg , thanks for getting back!!

Sorry, I didn’t mean to confuse, let me re-iterate. If I don’t specify the timestamp:

  name_override = "interval"
  tagexclude = ["url", "host"]
  data_format = "json_v2"
      [[inputs.http.json_v2]]
          [[inputs.http.json_v2.object]]
          path = "data"
          #timestamp_key = "time"
          #timestamp_format = "2023-05-07T19:30:00"
          included_keys = [
            "value",
            "time"
          ]

telegraf - test returns the following:

interval time="2023-05-07T20:20:00",value=11093.7 1683491942000000000

I meant to say that I don’t need the current time, i.e. the last column in unix / epoch time since that is off from the actual, sensor provided measurement. But when I specify the timeformat:

  name_override = "interval"
  tagexclude = ["url", "host"]
  data_format = "json_v2"
      [[inputs.http.json_v2]]
          [[inputs.http.json_v2.object]]
          path = "data"
          timestamp_key = "time"
          timestamp_format = "2023-05-07T19:30:00"
          included_keys = [
            "value",
            "time"
          ]

telegraf - test returns nothing beyond:

(...)
2023-05-07T20:40:15Z W! Outputs are not used in testing mode!
2023-05-07T20:40:15Z I! Tags enabled: host=hostname

I didn’t look into verbose logging, I’m just assuming that the timestamp_format can’t deal with the quotes the datetime is wrapped in (can’t change the sensor json output I’m afraid).

"time": "2023-05-04T23:10:00",

doesn’t work.

"time": 2023-05-04T23:10:00,

should work based on other examples I’ve found (I haven’t fully confirmed with a local json / repro yet).

Just to confirm, you would assume that is the issue too, right? I.e. that timestamp_format can’t match values (iso / epoch) in quotes?

I’ve stumbled over execd but I was also looking at processors.regex, to apply a simple regex to remove the quotes, I just didn’t find how to get a value from the json and then use the processed value as an input for timestamp_format. I’ll look at read some more if you say that is possible.

Sorry for my ignorance and thanks again for your help!

Valentin

The timestamp format needs to be in Go “reference time” which is defined to be the specific time: Mon Jan 2 15:04:05 MST 2006

Therefore you need to use:

timestamp_format = "2006-01-02T15:04:05"

Ouch.

I am an idiot. I swear I copied the right datetime initially but I didn’t when I created a new config.

Sorry for wasting everyone’s time. Any favorite charitable cause I can send some money towards?

1 Like

No you aren’t! It happens :slight_smile: