Product

Converting local time to ISO 8601 time in Elasticsearch

By
Alex Marquardt

If a timestamp field is sent into Elasticsearch without any timezone information, then it will be assumed to be UTC time (Coordinated Universal Time). However, if the timestamp actually represents a local time, then assuming that it is UTC will likely cause problems when displaying it in Kibana or other applications. In this blog I describe how to use an ingest pipeline to convert local timestamps into universal timestamps that conform to the ISO 8601 date and time format.

Converting timestamps from local time into universal time

If the local timezone of the data is known, then it is possible to use an ingest processor to convert from the local time to ISO 8601 format. Below is an example ingest pipeline that uses the date processor to convert the field called my_time from Europe/Madrid time into ISO 8601 (UTC + offset) format. 

PUT _ingest/pipeline/chage_local_time_to_iso
{
  "processors": [
    {
      "date" : {
        "field" : "my_time",
        "target_field": "my_time", 
        "formats" : ["dd/MM/yyyy HH:mm:ss"],
        "timezone" : "Europe/Madrid"
      }
    }
  ]
}

In order to test the above pipeline, we can execute the following code to simulate inserting a document: 

POST _ingest/pipeline/chage_local_time_to_iso/_simulate
{
  "docs": [
    {
      "_source": {
        "my_time": "12/10/2019 21:31:12",
        "other_field": "whatever"
      }
    }
  ]
}

The above will respond with output that looks as follows, which we can see has the correct ISO 8601 offset for Europe/Madrid for October 12th when daylight savings is in effect. As expected, the field my_time shows an offset of +02:00. 

{
  "docs" : [
    {
      "doc" : {
        "_index" : "_index",
        "_type" : "_doc",
        "_id" : "_id",
        "_source" : {
          "my_time" : "2019-10-12T21:31:12.000+02:00",
          "other_field" : "whatever"
        },
        "_ingest" : {
          "timestamp" : "2019-10-16T19:28:20.999077Z"
        }
      }
    }
  ]
}

We can also verify that the above pipeline is respecting daylight savings (which ends on October 27th in Spain) by submitting a document with a date of October 30th as follows: 

POST _ingest/pipeline/chage_local_time_to_iso/_simulate
{
  "docs": [
    {
      "_source": {
        "my_time": "30/10/2019 21:31:12",
        "other_field": "whatever"
      }
    }
  ]
}

Which responds with the following output, that has an offset of +01:00 as expected since daylight savings is no longer in effect: 

{
  "docs" : [
    {
      "doc" : {
        "_index" : "_index",
        "_type" : "_doc",
        "_id" : "_id",
        "_source" : {
          "my_time" : "2019-10-30T21:31:12.000+01:00",
          "other_field" : "whatever"
        },
        "_ingest" : {
          "timestamp" : "2019-10-16T19:30:40.401948Z"
        }
      }
    }
  ]
}

Finally, we can insert a "real" document into Elasticsearch as follows: 

PUT test_index/_doc/1?pipeline=chage_local_time_to_iso
{
  "my_time": "15/10/2019 01:11:55",
  "other_field": "whatever"
}

And we can retrieve the document as follows: 

GET test_index/_doc/1

Which should respond with the following, that has the correct offset of +02:00 on Oct 15th for the Europe/Madrid timezone: 

{
  "_index" : "test_index",
  "_type" : "_doc",
  "_id" : "1",
  "_version" : 1,
  "_seq_no" : 0,
  "_primary_term" : 1,
  "found" : true,
  "_source" : {
    "my_time" : "2019-10-15T01:11:55.000+02:00",
    "other_field" : "whatever"
  }
}

Conclusion

In this blog I have demonstrated how an ingest pipeline can be used to ingest data into Elasticsearch with unambiguous ISO 8601 timestamps. Give it a try in your cluster, or spin up a 14-day free trial of Elasticsearch Service and test it out in there. If you have any questions about ISO 8601 timestamps in Elasticsearch documents, or any other Elasticsearch-related topics, have a look at our Discuss forums for valuable insights and information.