Vegalite unable to parse dates in format DD/MM/YYYY

My log files have the timestamp as 13/10/2020 13:03 and Vegalite visualizations are being broken every time date is more than 12, which means it is taking month as days and vice versa.

Following is the line of code I am using for conversion:

    transform: [
      {
        calculate: "toDate(datum._source.TIMESTAMP)", 
        as: "time"
      }
    ]

TIMESTAMP attribute in the source is as follows:

    "TIMESTAMP" : "15/06/2020 17:18"

I don't think toDate function recognizes your format so you need to parse it.

Specification

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "data": [
    {
      "name": "table",
      "values": [{"TIMESTAMP": "15/06/2020 17:18"}],
      "transform": [
        {
          "type": "formula",
          "expr": "timeParse(datum.TIMESTAMP, '%d/%m/%Y %H:%M')",
          "as": "time"
        }
      ]
    }
  ]
}

Returns

** You may need to do another transform for Time Zone depending on your use case. Also I did this in Vega, just need to change it back to calculate vs formula.

1 Like

Thanks @aaron-nimocks for this, really helpful. Just one more query, how can I quickly debug this (the way you showed what it returns)?

Use the Vega Editor and on the bottom right click Data Viewer and select the data source by the name you set in the spec.

If you are using a newer version of Kibana you can also do it on there.

1 Like

Thanks so much @aaron-nimocks. Apart from solving the issue, this thread has been helpful in knowing about the Debug feature which I know will be extremely useful in future.

1 Like